Ben Ben - 6 months ago 14
MySQL Question

How to find the next record after a specified one in SQL?

I'd like to use a single SQL query (in MySQL) to find the record which comes after one that I specify.

I.e., if the table has:

id, fruit
-- -----
1 apples
2 pears
3 oranges


I'd like to be able to do a query like:

SELECT * FROM table where previous_record has id=1 order by id;


(clearly that's not real SQL syntax, I'm just using pseudo-SQL to illustrate what I'm trying to achieve)

which would return:

2, pears


My current solution is just to fetch all the records, and look through them in PHP, but that's slower than I'd like. Is there a quicker way to do it?

I'd be happy with something that returned two rows -- i.e. the one with the specified value and the following row.

EDIT: Sorry, my question was badly worded. Unfortunately, my definition of "next" is not based on ID, but on alphabetical order of fruit name. Hence, my example above is wrong, and should return oranges, as it comes alphabetically next after apples. Is there a way to do the comparison on strings instead of ids?

Answer

After the question's edit and the simplification below, we can change it to

SELECT id FROM table WHERE fruit > 'apples' ORDER BY fruit LIMIT 1
Comments