SQL Server: How to get previous data-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

there are many ways to do it. Here are 2

Self Join:

If ID is not continuous, use row_number() to generate one

select  p.ID as PrevID, p.Quarter as PrevQuarter, 
        c.ID as CurrID, c.Quarter as CurrQuarter
from    #test c
        left join #test p on c.ID = p.ID   1
where   c.ID = 4

using APPLY():

select p.ID as PrevID, p.Quarter as PrevQuarter, 
       c.ID as CurrID, c.Quarter as CurrQuarter
from   #test c
       outer apply
       (
           select top 1 ID, Quarter
           from   #test x
           where  x.ID < c.ID
           order by x.ID desc
       ) p
where c.ID = 4