I'm working on Laravel Framework 8.73.2 project with mysql Ver 8.0.26-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu)).
I'm getting wrong data from the database when using the offset condition. On the third data request I get unique data, but on the fourth data request I get repeated data
third data request, offset = 20
select * from `users` where `users`.`deleted_at` is null order by `created_at` desc limit 10 offset 20
| id | name | created_at |
|---|---|---|
| '206' | 'Taryn' | '2021-12-04 23:58:38' |
| '207' | 'Teresa' | '2021-12-04 23:58:38' |
| '208' | 'Arthur' | '2021-12-04 23:58:38' |
| '210' | 'Janick' | '2021-12-04 23:58:38' |
| '209' | 'Ambrose' | '2021-12-04 23:58:38' |
| '212' | 'Katrina' | '2021-12-04 23:58:38' |
| '211' | 'Alexander' | '2021-12-04 23:58:38' |
| '199' | 'Maximo' | '2021-12-04 23:49:56' |
| '200' | 'Herman' | '2021-12-04 23:49:56' |
| '198' | 'Tanner' | '2021-12-04 23:49:56' |
fourth data request, offset = 30
select * from `users` where `users`.`deleted_at` is null order by `created_at` desc limit 10 offset 30
Note that records 199, 198 e 200 are repeated here with the previous request.
| id | name | created_at |
|---|---|---|
| '201' | 'Amani' | '2021-12-04 23:49:56' |
| '200' | 'Herman' | '2021-12-04 23:49:56' |
| '199' | 'Maximo' | '2021-12-04 23:49:56' |
| '198' | 'Tanner' | '2021-12-04 23:49:56' |
| '196' | 'Prof.' | '2021-12-04 23:49:56' |
| '195' | 'Murray' | '2021-12-04 23:49:56' |
| '194' | 'Prof.' | '2021-12-04 23:49:56' |
| '188' | 'Ms.' | '2021-12-04 23:49:23' |
| '184' | 'Alanis' | '2021-12-04 23:49:23' |
| '185' | 'Percy' | '2021-12-04 23:49:23' |
How can I fix this issue and bring the correct data?
All records
| id | name | created_at |
|---|---|---|
| '45' | 'Adminis' | '2021-04-14 17:31:30' |
| '147' | 'Amal Cruz' | '2021-11-29 01:40:59' |
| '165' | 'Whitakerg' | '2021-11-30 23:13:05' |
| '166' | 'Dela cruz' | '2021-12-02 23:28:49' |
| '167' | 'Dalek990' | '2021-12-04 18:14:22' |
| '168' | 'Banks2' | '2021-12-04 18:22:32' |
| '169' | 'Walton' | '2021-12-04 18:22:46' |
| '170' | 'Ballard' | '2021-12-04 18:22:56' |
| '171' | 'Jennings' | '2021-12-04 18:23:12' |
| '172' | 'Williamson' | '2021-12-04 18:23:20' |
| '173' | 'Sargent' | '2021-12-04 18:30:02' |
| '174' | 'Nicklaus' | '2021-12-04 23:48:06' |
| '175' | 'Chasity' | '2021-12-04 23:48:06' |
| '176' | 'Malinda' | '2021-12-04 23:48:06' |
| '177' | 'Amie' | '2021-12-04 23:48:06' |
| '178' | 'Nelson' | '2021-12-04 23:48:06' |
| '179' | 'Jaydon' | '2021-12-04 23:48:06' |
| '180' | 'Dr.' | '2021-12-04 23:48:06' |
| '181' | 'Dr.' | '2021-12-04 23:48:06' |
| '182' | 'Mr.' | '2021-12-04 23:48:06' |
| '183' | 'Miss' | '2021-12-04 23:48:06' |
| '184' | 'Alanis' | '2021-12-04 23:49:23' |
| '185' | 'Percy' | '2021-12-04 23:49:23' |
| '186' | 'Josue' | '2021-12-04 23:49:23' |
| '187' | 'Ms.' | '2021-12-04 23:49:23' |
| '188' | 'Ms.' | '2021-12-04 23:49:23' |
| '189' | 'Ezequiel' | '2021-12-04 23:49:23' |
| '190' | 'Filomena' | '2021-12-04 23:49:23' |
| '191' | 'Eldora' | '2021-12-04 23:49:23' |
| '192' | 'Prof.' | '2021-12-04 23:49:23' |
| '193' | 'Dr.' | '2021-12-04 23:49:23' |
| '194' | 'Prof.' | '2021-12-04 23:49:56' |
| '195' | 'Murray' | '2021-12-04 23:49:56' |
| '196' | 'Prof.' | '2021-12-04 23:49:56' |
| '197' | 'Kyra' | '2021-12-04 23:49:56' |
| '198' | 'Tanner' | '2021-12-04 23:49:56' |
| '199' | 'Maximo' | '2021-12-04 23:49:56' |
| '200' | 'Herman' | '2021-12-04 23:49:56' |
| '201' | 'Amani' | '2021-12-04 23:49:56' |
| '202' | 'Jeramy' | '2021-12-04 23:49:56' |
| '203' | 'Mrs.' | '2021-12-04 23:49:56' |
| '204' | 'Stuart' | '2021-12-04 23:58:38' |
| '205' | 'Junior' | '2021-12-04 23:58:38' |
| '206' | 'Taryn' | '2021-12-04 23:58:38' |
| '207' | 'Teresa' | '2021-12-04 23:58:38' |
| '208' | 'Arthur' | '2021-12-04 23:58:38' |
| '209' | 'Ambrose' | '2021-12-04 23:58:38' |
| '210' | 'Janick' | '2021-12-04 23:58:38' |
| '211' | 'Alexander' | '2021-12-04 23:58:38' |
| '212' | 'Katrina' | '2021-12-04 23:58:38' |
| '213' | 'Olin' | '2021-12-04 23:58:38' |
| '214' | NULL | '2021-12-21 00:34:25' |
| '215' | 'Jones' | '2021-12-23 01:23:05' |
| '216' | 'Jonh' | '2021-12-23 01:27:53' |
| '217' | 'Super' | '2021-12-23 01:32:59' |
| '218' | 'Mario' | '2021-12-23 01:35:14' |
| '219' | 'Juca' | '2021-12-23 01:40:16' |
| '220' | 'He' | '2021-12-23 01:47:07' |
| '221' | 'Jon' | '2021-12-23 01:48:19' |
| '222' | 'Vincent' | '2021-12-23 01:50:43' |
| '223' | 'Jaquelyn' | '2021-12-23 01:54:22' |
| '224' | 'Jackson' | '2021-12-23 01:55:05' |
| '225' | 'Sarah' | '2021-12-23 02:02:52' |
| '226' | 'Ryder' | '2021-12-23 22:46:42' |
| '227' | 'Remedios' | '2021-12-23 22:59:03' |
| '228' | 'Brenda' | '2021-12-23 23:03:43' |
| '229' | 'Frances' | '2021-12-23 23:07:12' |
| '230' | 'Raphael' | '2021-12-23 23:08:13' |
CodePudding user response：
Because the records are being sorted by their creation time, and there are multiple records that were created at the same time, “duplicates” can appear. The simplest option would be to include a second column to the sort:
ORDER BY `created_at` DESC, `id`
LIMIT 10 OFFSET 20
This will give you a more consistent response order