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!

which version ? if it is 2012 sql server than look at **lead && lag** http://blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/ — Royi Namir, Sep 27 '12 at 12:07
The query could be different but I want to create one stored procedure for all queries — Vytalyi, Sep 27 '12 at 12:09
again , for your question : what is the query used to get those results ? I need the order by clause here in order to give you an answer — Royi Namir, Sep 27 '12 at 12:10
have a look at my question about this topic http://stackoverflow.com/questions/12402524/does-sql-server-minds-the-way-records-where-inserted — Royi Namir, Sep 27 '12 at 12:12
Ok, as I understood I need to use row_number()... This will be my order clause. And how my query will look like in this case? — Vytalyi, Sep 27 '12 at 12:15
`row_number()` must use `order by` - again ! what is your order by clause ? :-) — Royi Namir, Sep 27 '12 at 12:16
SELECT clause with out the ORDER BY does not set any sorting at all. Have a look [here](http://stackoverflow.com/questions/8923366/getting-last-record-from-mysql?answertab=votes#tab-top). — Alberto De Caro, Sep 27 '12 at 12:44

AnandPhadke · Answer 1 · 2012-09-27T12:26:31.890

4

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

edited Sep 27 '12 at 12:26

answered Sep 27 '12 at 12:19

AnandPhadke

13,160
5
26
33

Something wrong with this query... It returns not what I want... Give me 3 minutest and I will attach the screenshot – Vytalyi Sep 27 '12 at 12:29
whatever you have explained in your question abobe its doing correctly – AnandPhadke Sep 27 '12 at 12:31
Could you please look on screenshot? Any ideas? – Vytalyi Sep 27 '12 at 12:33
cann you pls show only discussionthreadid,rn column in the screenshot there? – AnandPhadke Sep 27 '12 at 12:35
is is 708 and 710. Is it what you asking? – Vytalyi Sep 27 '12 at 12:37
i want to see rn column in the screen shot – AnandPhadke Sep 27 '12 at 12:37
I see that it very close to what I want... but could you please help me to finish this? – Vytalyi Sep 27 '12 at 12:59
I cant see the rn column there – AnandPhadke Sep 27 '12 at 13:15
just select discussionthreadid and rn columns from your table and put results in screenshot – AnandPhadke Sep 27 '12 at 13:20
yeah i got it.But did you check my Fiddle Demo there its working properly.can you put your table with sample data in fiddle i wil check – AnandPhadke Sep 27 '12 at 13:29
waht position i did not get you? – AnandPhadke Sep 27 '12 at 13:49
Mys solution should work for your requirement.I guess there is a small tweaking needs to be done for your table – AnandPhadke Sep 27 '12 at 14:06
if you could atleast give me your table structure i can try to solve it – AnandPhadke Sep 27 '12 at 14:07

score 3 · Answer 2 · answered Sep 27 '12 at 12:38

3

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.

answered Sep 27 '12 at 12:38

podiluska

50,950
7
98
104

As you said there will not be any order but as per my understanding when data gets stored in memory,it just appends to the last inserted row.So thats what OP also wants.I mean he wants to get previous and next row from heap. – AnandPhadke Sep 27 '12 at 13:19

t-clausen.dk · Accepted Answer · 2012-09-28T02:14:26.840

2

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

edited Sep 28 '12 at 02:14

answered Sep 27 '12 at 12:39

t-clausen.dk

43,517
12
59
92

I'm not sure I got what is s column. I have no this coulmn in table. Do you want me to create it? – Vytalyi Sep 27 '12 at 12:56
Id, Label, Date are the columns. I don't want you to create new columns, I just want you to populate the temporary table with the data you have, then you can link the rows to the previous rows – t-clausen.dk Sep 27 '12 at 13:06
Well, it works for previous, thanks! And how I can modify this to see next and previous at the same time? – Vytalyi Sep 27 '12 at 13:47
@Vytalyi I now noticed you wanted both the next and the previous, so i modified my script to include both – t-clausen.dk Sep 28 '12 at 02:19
Yeah, looks like it works. Not a standard solution but I think I can do this until find something better. Thanks a lot! – Vytalyi Sep 28 '12 at 08:10

Next/previous record based on current

3 Answers3