CODEWITHSUNDEEP
mysqlsqlselect
fatlinesofcode
fatlinesofcode

Reputation: 1667

How can I select adjacent row in sql when ordered by a different field?

I've got a table of data with the following structure:

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

If want to find the row next to #3 I can use :

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

However what I want to do is order by table by likes. When the data is ordered by likes it looks like this

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

How can I select the rows before or after a certain id when ordered by likes? e.g. for id 5, my result would be row id 3 before and row id 2 after.

Upvotes: 4

Views: 1536

Answers (2)

Karthikeyan
Karthikeyan

Reputation: 990

Your second table shows wrong ids 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 tries to simulate the row number, which is missing in MySQL, but available in MSSQL, Oracle and others.

Upvotes: 0

RiaD
RiaD

Reputation: 47619

If likes are unique numbers, following should work.

previous:

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

next:

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

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

Upvotes: 1

Related Questions