Next/previous record based on current

Question

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!

Answer 1

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

Answer 2

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.