CODEWITHSUNDEEP
mysql
Stephen Bouffe
Stephen Bouffe

Reputation: 517

How to find the next or previous or next row

I have a table with customer details

id,title,first_name,surname

When I use

SELECT * FROM customers ORDER BY surname,first_name,title;

How can I get the previous or next row based on the id without reading the entire table.

Upvotes: 0

Views: 116

Answers (2)

Alfa3eta
Alfa3eta

Reputation: 405

next:

SELECT * FROM customers WHERE id > XYZ ORDER BY id ASC LIMIT 1

previous:

SELECT * FROM customers WHERE id < XYZ ORDER BY id DESC LIMIT 1

where XYZ is your number

Upvotes: 2

Habibillah
Habibillah

Reputation: 28695

If the ID type is integer and nextID = currentID + 1. so you can write your query as:

SELECT * 
FROM customers 
WHERE id = (currentID + 1) 
ORDER BY surname,first_name,title;

Upvotes: 0

Related Questions