SELECT id, title, sort FROM my_table WHERE id = 1 AND SELECT id, title FROM my_table WHERE sort is > *sort from first select* LIMIT 1. I'm trying to select a row from my table and then select another row based on the contents of the first select, can I do this? Or would it have to be two queries?
Basically, I'm trying to select row with id 1 and then get it's sort value, then find the row which has the next sort value above it.
You can do it but retrieving it in one row:
SELECT * FROM my_table t1 JOIN my_table t2 ON (t2.sort>t1.sort) WHERE t1.title="something you like" ORDER BY t2.sort DESC LIMIT 1; This is what I would suggest:
SELECT id, title, sort FROM my_table WHERE sort > (SELECT sort FROM my_table WHERE id = 1) Easiest way would be
SELECT t1.id as Table1_id , t1.title as Table1_title , t1.sort as Table1_sort , t2.id as Table2_id, t2.title as Table2_title FROM my_table t1 , my_table t2 WHERE t1.id = 1 AND t2.sort > t1.sort LIMIT 1. This will produce your desired output.
