How to select the adjacent row in sql when ordering in another field?

Question

I have a data table with the following structure:

id | likes 1 | 2 2 | 5 3 | 2 4 | 6 5 | 2 

If you want to find the line next to number 3, I can use:

 SELECT * FROM table WHERE id >= 3 ORDER BY id 

However, what I want to do is to sort the ones I like by the table. When the data is ordered by the form you like, it looks like

 id | likes 1 | 2 3 | 2 5 | 2 2 | 5 4 | 6 

How can I select the lines before or after a certain identifier when I order it? for example for id 5, my result will be line id 3 before and line id 2 after.

Answer 1

If you like unique numbers, then the following should work.

previous:

 SELECT * FROM table WHERE likes < (SELECT likes FROM table WHERE id = ID) ORDER BY likes DESC LIMIT 1 

following:

 SELECT * FROM table WHERE likes > (SELECT likes FROM table WHERE id = ID) ORDER BY likes ASC LIMIT 1 

You can change 1 of them to <= or> = and add WHERE id != ID

Answer 2

The second table shows the wrong identifiers for the first two rows, by the way.

It should be:

 id likes 1 2 3 2 

This works in MySQL for me:

 Select id, likes from (SELECT id, @rownum: =@rownum +1 AS rownum, likes FROM table u, (SELECT @rownum:=0) r ORDER BY likes) as derived where rownum >= 2 and rownum <= 4; (SELECT id, @rownum: =@rownum +1 AS rownum, likes FROM table u, (SELECT @rownum:=0) r ORDER BY likes); 

The last part attempts to simulate a row number that is not available in MySQL, but is available in MSSQL, Oracle, and others.