MySQL 8.0

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

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