Geek asks and answers

How to get the “next row” and “previous row” in a result set without selecting the entire result set?

There is a proper way to do this:

ID | name
 1 | aa
 4 | bb
 6 | dd
 9 | ee

The identifier is the main index with auto_increment ... the missing indexes were SQL DELETEd, so there are some empty spaces

if i navigate the page? ID = 4 I want to get the previous line (with identifier 1) and the next (with identifier 6) (added :) using the same query

is there any way to do this without selecting / moving the entire result set?

Thank you in advance!

+3
source share
3 answers
SELECT * from table where `ID` > 4 ORDER BY `ID` ASC LIMIT 1 /* next */
SELECT * from table where `ID` < 4 ORDER BY `ID` DESC LIMIT 1 /* previous */
+3
source

I would do something like this (for the previous post):

SELECT * FROM table
WHERE id < @id
ORDER BY id DESC
LIMIT 1

.

+3

Try it -

(SELECT * FROM table_name
WHERE id < 4
ORDER BY id DESC
LIMIT 1)
union 
(SELECT * FROM table_name
WHERE id > 4
ORDER BY id ASC
LIMIT 1)
+2
source

More articles:


All Articles