CODEWITHSUNDEEP
mysql
synccm2012
synccm2012

Reputation: 497

How to achieve next and previous row in a query

I have a table like this in MYSQL:

SELECT * FROM test_table 

id  u_id  name   date
1   101   name2  2012-05-14
2   305   name3  2012-05-11
3   506   name4  2012-05-05
4   207   name5  2012-05-12
5   108   name6  2012-05-03

SELECT id,u_id from test_table order by date;

id  u_id

5    108
3    506
2    305
4    207
1    101

I have a application where where these things are displayed

on clicking on any u_id it takes me to a different page which displyed details stuff Now I want to write a query which willl give me the next record

How do I achive the next record from here like

when I say u_id>305 it shoud give me 207? and u_id<305 it should give me 506

Upvotes: 0

Views: 90

Answers (3)

theon
theon

Reputation: 14420

Given the u_id is 305, to get the next record by date:

SELECT id,u_id 
FROM test_table a
WHERE a.date > (SELECT date FROM test_table b WHERE b.u_id = 305)
ORDER BY a.date
LIMIT 1;

And to get the previous record by date:

SELECT id,u_id 
FROM test_table a
WHERE a.date < (SELECT date FROM test_table b WHERE b.u_id = 305)
ORDER BY a.date DESC
LIMIT 1;

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24096

try this:

 select id,u_id from
  (SELECT id,u_id,@rownum:= @rownum+1 AS Sno 
  from test_table , (SELECT @rownum:=0) r;
  order by date)a
  where Sno=<@ID-1/+1>

lets say if your current Sno=5 then 4 will give the previous record and 6 will give the next record

Upvotes: 0

Omesh
Omesh

Reputation: 29121

I think this is what you are looking for:

SELECT id, u_id 
FROM test_table 
WHERE uid > 305
ORDER BY date ASC
LIMIT 1;

SELECT id, u_id 
FROM test_table 
WHERE uid < 305
ORDER BY date DESC
LIMIT 1;

Upvotes: 2

Related Questions