SQL Server: How to get previous data

April 20, 2022

I know that LEAD & LAG functions are there to achieve the task to get previous & next data row. my question is how to achieve the same goal in old sql server where LEAD & LAG not present. please suggest some good approach to retrieve Previous & Next data row with LEAD & LAG function. Thanks

Here i am sharing one example which is working fine.

Create table #test
  (
  ID int identity(1,1),
  Quarter nvarchar(20)
  )
  insert into #test values
  ('1Q 2010'),
   ('2Q 2010'),
    ('3Q 2010'),
     ('4Q 2010'),
   ('FY 2010')
 select * from #test
    
 select PrevID,PrevQuarter,CurrID,CurrQuarter
 from 
 (
 select Lag(ID,1) over(order by ID)PrevID ,LAG(Quarter,1)over(order by ID)PrevQuarter,
 ID as CurrID,Quarter as CurrQuarter 
 from #test
 )t
 where CurrID=4

>Solution :

You could use correlated TOP subqueries in lieu of LEAD and LAG:

SELECT PrevID, PrevQuarter, CurrID, CurrQuarter
FROM
(
    SELECT
        (SELECT TOP 1 ID FROM #test t2
         WHERE t2.ID < t1.ID ORDER BY t2.ID DESC) PrevID,
        (SELECT TOP 1 Quarter FROM #test t2
         WHERE t2.ID < t1.ID ORDER BY t2.ID DESC) PrevQuarter,
        ID AS CurrID, Quarter AS CurrQuarter
    FROM #test t1
) t
WHERE CurrID = 4;

Here is a working demo.