How to get row_number from an id in certain order condition in MYSQL?

Question

I am looking for a MYSQL paging solution with last_id and order condition, to use LIMIT, OFFSET I need to figure out the offset for the last_id.

For example, I have a table news (id, title, updated_at), the first page returns 3,1,2,5,4, for the second page I have 3 arguments (last_id is 4, order by updated_at desc, limit is 5), I need to figure out the offset for the id 4 order by updated_at desc.

+------+-------+---------------------+
| id   | title | updated_at          |
+------+-------+---------------------+
|    3 | test3 | 2018-01-22 11:30:08 |
+------+-------+---------------------+
|    1 | test1 | 2018-01-21 12:30:08 |
+------+-------+---------------------+
|    2 | test2 | 2018-01-20 12:30:08 |
+------+-------+---------------------+
|    5 | test5 | 2018-01-19 13:30:08 |
+------+-------+---------------------+
|    4 | test4 | 2018-01-18 14:30:08 |
+------+-------+---------------------+

....

+------+-------+---------------------+
|    11 | test11 | 2018-01-17 14:30:08 |
+------+-------+---------------------+
|    12 | test12 | 2018-01-16 15:30:08 |
+------+-------+---------------------+

What I want to return for the second page is 11,12,...., is there any efficient way to achieve it?

Sample data and desired results would really clarify what you want to do. — Gordon Linoff, Jan 22 '18 at 03:26
Possible duplicate of [MySQL Data - Best way to implement paging?](https://stackoverflow.com/questions/3799193/mysql-data-best-way-to-implement-paging) — e_i_pi, Jan 22 '18 at 03:48
@e_i_pi, it is a little different from that, probably my question should be how to find row_number() for a given id and order condition? — seaguest, Jan 22 '18 at 03:51
You'll still just use a `LIMIT 10 OFFSET n` statement through, regardless of the `ORDER BY` statement, where `n = (#pages - 1) * results_per_page` — e_i_pi, Jan 22 '18 at 03:53

score 0 · Answer 1 · answered Jan 23 '18 at 02:43

To figure out the offset for the id 4 order by updated_at desc:

SELECT @rownum:=@rownum + 1 AS offset, id, title, updated_at 
FROM news, (SELECT @rownum:=0) AS r 
ORDER BY updated_at DESC;

Now you get the offset of the specific row.

Then you can do something like:

SELECT * FROM 
(SELECT @rownum:=@rownum + 1 AS offset, id, title, updated_at 
 FROM news, (SELECT @rownum:=0) AS r ORDER BY updated_at DESC) AS t 
WHERE offset > 5;

score 0 · Answer 2 · answered Jan 23 '18 at 06:30

0

It seems it is not efficient using SQL, maybe order news by code is much more appropriate.

answered Jan 23 '18 at 06:30

seaguest

2,510
5
27
45

How to get row_number from an id in certain order condition in MYSQL?

2 Answers2