CODEWITHSUNDEEP
mysql
Ajay_kumar
Ajay_kumar

Reputation: 11

How to find prev and next id record from table

How to know prev id value and next id value from a table where id is given ?

like ia have id=5 for a recod in table then i want to know the prev id value and next id value

Upvotes: 1

Views: 477

Answers (3)

neiks
neiks

Reputation: 1

SELECT * FROM table WHERE id = ( SELECT min(id) FROM table WHERE id > id )
SELECT * FROM table WHERE id = ( SELECT max(id) FROM table WHERE id < id )

Upvotes: 0

Brian Hooper
Brian Hooper

Reputation: 22084

Perhaps something like...

SELECT MAX(id) AS previous
    FROM my_table
    WHERE id < 5;

and

SELECT MIN(id) AS next
    FROM my_table
    WHERE id > 5;

would get what you want.

Upvotes: 1

Pekka
Pekka

Reputation: 449783

If id is an auto-increment INT field,

SELECT * FROM tablename WHERE id < 5 ORDER by ID DESC LIMIT 0,1;

SELECT * FROM tablename WHERE id > 5 ORDER by ID ASC LIMIT 0,1;

should do the job.

Upvotes: 1

Related Questions