KSK KSK - 4 months ago 14
SQL Question

Select pairs of rows in SQL (Should work in SQL Server 2008 and 2012)

I need to pick pairs of rows from a table depending on values in one column.
This is my source table:

EntryID | Code
-----------------
11 | 40
22 | 100
23 | 80
24 | 60
35 | 90
46 | 80
47 | 30
58 | 80
69 | 20
110 | 40


I need to extract only the rows where the code is 80 and the first row after the code 80. So this is the output I seek:

Code
------
80
60
80
30
80
20


I can achieve this using cursors but my actual table has 1 million+ records. So cursors are definitely not an option.

I have so far done this:

declare @currentCode int
declare @rowPtr int
declare @code80row bit
declare @nextRowTaken bit
declare @T1 table (RowNum int, Code int)
declare @Final table (RowNum int, Code int)

set @rowPtr = 1
set @code80row = 0
set @nextRowTaken = 0

insert into @T1 select ROW_NUMBER() over (order by EntryID desc) RowNum, Code from Codes
set @currentCode = (select code from @t1 where RowNum = @rowPtr)
while @currentCode > 0
begin
if @currentCode = 80
begin
set @code80row = 1
set @nextRowTaken = 0
insert into @Final(RowNum, Code) values (@rowPtr, @currentCode)
end
else if (@code80row = 1 and @nextRowTaken = 0)
begin
set @code80row = 0
set @nextRowTaken = 1
insert into @Final(RowNum, Code) values (@rowPtr, @currentCode)
end
set @rowPtr = @rowPtr + 1
set @currentCode = (select code from @t1 where RowNum = @rowPtr)
end
select * from @Final


Is there a better way to get this result?

EDIT:
Following @Serg's reply below, before he added the very last line, I tried this also.... This may help someone with different needs:

;with cte as (
select Row_Number() over (order by EntryID) RowNum, entryid, Code from Codes
)
select ft.RowNum, st.RowNum, ft.entryid, st.entryid, ft.Code, st.Code
from cte as ft join cte as st
on ft.code = 80 and ft.RowNum = st.RowNum-1


which gives:

RowNum |RowNum |entryid |entryid |Code |Code
--------------------------------------------
3 |4 |23 |24 |80 |60
6 |7 |46 |47 |80 |30
8 |9 |58 |69 |80 |20

Answer

If you are on 2008 and no lead/lag

with cte as (
    select rn=ROW_NUMBER() over (order by EntryID desc), EntryID, Code 
    from Codes
)
select t2.*
from cte c1 
cross apply ( 
      select EntryID, Code 
      from cte c2 
      where c2.rn between c1.rn and c1.rn+1
) t2
where c1.Code=80;
Comments