By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test_table (
phone_number BIGINT,
entry_date DATETIME);
INSERT INTO test_table
WITH RECURSIVE cte AS (
SELECT 1 AS pn, NOW() AS ed UNION ALL
SELECT pn+1, ed + INTERVAL pn+1 HOUR FROM cte WHERE pn+1 < 50)
SELECT FLOOR(pn*0.08), ed FROM cte
SELECT phone_number, entry_date FROM
(SELECT phone_number, entry_date,
ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY entry_date) AS Rownum
FROM test_table) AS subquery1
WHERE Rownum <=10
ORDER BY phone_number, entry_date DESC;
| phone_number | entry_date |
|---|---|
| 0 | 2021-07-09 15:46:44 |
| 0 | 2021-07-09 05:46:44 |
| 0 | 2021-07-08 20:46:44 |
| 0 | 2021-07-08 12:46:44 |
| 0 | 2021-07-08 05:46:44 |
| 0 | 2021-07-07 23:46:44 |
| 0 | 2021-07-07 18:46:44 |
| 0 | 2021-07-07 14:46:44 |
| 0 | 2021-07-07 11:46:44 |
| 0 | 2021-07-07 09:46:44 |
| 1 | 2021-07-17 21:46:44 |
| 1 | 2021-07-16 23:46:44 |
| 1 | 2021-07-16 02:46:44 |
| 1 | 2021-07-15 06:46:44 |
| 1 | 2021-07-14 11:46:44 |
| 1 | 2021-07-13 17:46:44 |
| 1 | 2021-07-13 00:46:44 |
| 1 | 2021-07-12 08:46:44 |
| 1 | 2021-07-11 17:46:44 |
| 1 | 2021-07-11 03:46:44 |
| 2 | 2021-08-01 03:46:44 |
| 2 | 2021-07-30 17:46:44 |
| 2 | 2021-07-29 08:46:44 |
| 2 | 2021-07-28 00:46:44 |
| 2 | 2021-07-26 17:46:44 |
| 2 | 2021-07-25 11:46:44 |
| 2 | 2021-07-24 06:46:44 |
| 2 | 2021-07-23 02:46:44 |
| 2 | 2021-07-21 23:46:44 |
| 2 | 2021-07-20 21:46:44 |
| 3 | 2021-08-23 08:46:44 |
| 3 | 2021-08-21 09:46:44 |
| 3 | 2021-08-19 11:46:44 |
| 3 | 2021-08-17 14:46:44 |
| 3 | 2021-08-15 18:46:44 |
| 3 | 2021-08-13 23:46:44 |
| 3 | 2021-08-12 05:46:44 |
| 3 | 2021-08-10 12:46:44 |
| 3 | 2021-08-08 20:46:44 |
| 3 | 2021-08-07 05:46:44 |
SELECT phone_number, entry_date FROM
(SELECT phone_number, entry_date,
@row_number:=CASE
WHEN @pn = phone_number THEN @row_number + 1
ELSE 1 END AS rownum,
@pn:=phone_number PN
FROM test_table t
CROSS JOIN (SELECT @pn:=0, @row_number:=0) as n
ORDER BY entry_date) AS subquery1
WHERE Rownum <=10
ORDER BY phone_number, entry_date DESC;
| phone_number | entry_date |
|---|---|
| 0 | 2021-07-09 15:46:44 |
| 0 | 2021-07-09 05:46:44 |
| 0 | 2021-07-08 20:46:44 |
| 0 | 2021-07-08 12:46:44 |
| 0 | 2021-07-08 05:46:44 |
| 0 | 2021-07-07 23:46:44 |
| 0 | 2021-07-07 18:46:44 |
| 0 | 2021-07-07 14:46:44 |
| 0 | 2021-07-07 11:46:44 |
| 0 | 2021-07-07 09:46:44 |
| 1 | 2021-07-17 21:46:44 |
| 1 | 2021-07-16 23:46:44 |
| 1 | 2021-07-16 02:46:44 |
| 1 | 2021-07-15 06:46:44 |
| 1 | 2021-07-14 11:46:44 |
| 1 | 2021-07-13 17:46:44 |
| 1 | 2021-07-13 00:46:44 |
| 1 | 2021-07-12 08:46:44 |
| 1 | 2021-07-11 17:46:44 |
| 1 | 2021-07-11 03:46:44 |
| 2 | 2021-08-01 03:46:44 |
| 2 | 2021-07-30 17:46:44 |
| 2 | 2021-07-29 08:46:44 |
| 2 | 2021-07-28 00:46:44 |
| 2 | 2021-07-26 17:46:44 |
| 2 | 2021-07-25 11:46:44 |
| 2 | 2021-07-24 06:46:44 |
| 2 | 2021-07-23 02:46:44 |
| 2 | 2021-07-21 23:46:44 |
| 2 | 2021-07-20 21:46:44 |
| 3 | 2021-08-23 08:46:44 |
| 3 | 2021-08-21 09:46:44 |
| 3 | 2021-08-19 11:46:44 |
| 3 | 2021-08-17 14:46:44 |
| 3 | 2021-08-15 18:46:44 |
| 3 | 2021-08-13 23:46:44 |
| 3 | 2021-08-12 05:46:44 |
| 3 | 2021-08-10 12:46:44 |
| 3 | 2021-08-08 20:46:44 |
| 3 | 2021-08-07 05:46:44 |
CREATE TABLE new_table (
phone_number BIGINT,
entry_date DATETIME);
INSERT INTO new_table
SELECT phone_number, entry_date FROM
(SELECT phone_number, entry_date,
@row_number:=CASE
WHEN @pn = phone_number THEN @row_number + 1
ELSE 1 END AS rownum,
@pn:=phone_number PN
FROM test_table t
CROSS JOIN (SELECT @pn:=0, @row_number:=0) as n
ORDER BY entry_date) AS subquery1
WHERE Rownum <=10
ORDER BY phone_number, entry_date DESC;
SELECT * FROM new_table
| phone_number | entry_date |
|---|---|
| 0 | 2021-07-09 15:46:44 |
| 0 | 2021-07-09 05:46:44 |
| 0 | 2021-07-08 20:46:44 |
| 0 | 2021-07-08 12:46:44 |
| 0 | 2021-07-08 05:46:44 |
| 0 | 2021-07-07 23:46:44 |
| 0 | 2021-07-07 18:46:44 |
| 0 | 2021-07-07 14:46:44 |
| 0 | 2021-07-07 11:46:44 |
| 0 | 2021-07-07 09:46:44 |
| 1 | 2021-07-17 21:46:44 |
| 1 | 2021-07-16 23:46:44 |
| 1 | 2021-07-16 02:46:44 |
| 1 | 2021-07-15 06:46:44 |
| 1 | 2021-07-14 11:46:44 |
| 1 | 2021-07-13 17:46:44 |
| 1 | 2021-07-13 00:46:44 |
| 1 | 2021-07-12 08:46:44 |
| 1 | 2021-07-11 17:46:44 |
| 1 | 2021-07-11 03:46:44 |
| 2 | 2021-08-01 03:46:44 |
| 2 | 2021-07-30 17:46:44 |
| 2 | 2021-07-29 08:46:44 |
| 2 | 2021-07-28 00:46:44 |
| 2 | 2021-07-26 17:46:44 |
| 2 | 2021-07-25 11:46:44 |
| 2 | 2021-07-24 06:46:44 |
| 2 | 2021-07-23 02:46:44 |
| 2 | 2021-07-21 23:46:44 |
| 2 | 2021-07-20 21:46:44 |
| 3 | 2021-08-23 08:46:44 |
| 3 | 2021-08-21 09:46:44 |
| 3 | 2021-08-19 11:46:44 |
| 3 | 2021-08-17 14:46:44 |
| 3 | 2021-08-15 18:46:44 |
| 3 | 2021-08-13 23:46:44 |
| 3 | 2021-08-12 05:46:44 |
| 3 | 2021-08-10 12:46:44 |
| 3 | 2021-08-08 20:46:44 |
| 3 | 2021-08-07 05:46:44 |