CODEWITHSUNDEEP
sqlsql-server-2008
Vytalyi
Vytalyi

Reputation: 1695

Next/previous record based on current

I have a table which is not sorted by any of column. Is there any way to select next/previous record if I know only Id of current? (I'm using mssql)

Id     Label     Date
---------------------
1      label1    2011-01-10
7      label2    2011-01-15 -- how to get previous?
5      label3    2011-01-12 -- I know id of this record
10     label10   2011-01-25 -- how to get next?
12     label8    2011-01-13
2      label5    2011-01-29

Thanks in advance!

Upvotes: 3

Views: 2400

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

If you really want the previous from the list you enclosed, here is a way.

declare @t table(Id int, Label varchar(10), Date date, s int identity(1,1))
insert @t (id, label, date) 
values(1,'label1','2011-01-10'),(7,'label2','2011-01-15'),
(5,'label3','2011-01-12'),(10,'label10','2011-01-25'),
(12,'label8','2011-01-13'),(2,'label5','2011-01-29')

--select the data with a self join

select t1.id as previous_id, t2.id, t2.Label, t2.Date, t3.id, t3.id as next_id
from @t t1
right join
@t t2 on t1.s + 1 = t2.s
left join
@t t3  on t2.s = t3.s - 1

Upvotes: 2

podiluska
podiluska

Reputation: 51494

If it is not sorted by any column, there is no definitive next or previous record. Data in SQL Server has no order, other than that specified by an ORDER BY clause.

Upvotes: 3

AnandPhadke
AnandPhadke

Reputation: 13496

try this:

VALUES (1, 'label1', '2011-01-10'), (7, 'label2', '2011-01-15'),
       (5, 'label3', '2011-01-12'), (10, 'label10', '2011-01-25'),             
       (12, 'label8', '2011-01-13'), (2, 'label5', '2011-01-29')

select * from table007; 

Declare @inptID int=12;

;WITH CTE 
as
(
   select *, ROW_NUMBER() over (order by (select 0)) as rn 
   from table007
 )

select * 
from CTE 
where rn in( select rn-1 from CTE where id = @inptID)
union all
select * from CTE where rn in(select rn + 1 from CTE where id = @inptID);

SQL Fiddle Demo

DEMO

Upvotes: 4

Related Questions