CODEWITHSUNDEEP
sql-serversql-server-2000laglead
Irfan
Irfan

Reputation: 695

Is anything like LAG() or LEAD() in SQL Server 2000

I want to get the next row value in current row. Just like LEAD() does in latest SQL Server. But I am currently using SQL Server 2000. And I don't have any other option.

Actual scenario is:

TableRule:

#   HeadNo | NextHeadNo |     NoFrom
-----------+------------+------------
1    AA    |    AB      |        1
2    AB    |    AC      |        1
3    AC    |    AX      |        1
4    AD    |    AE      |        1   ****and so on

I want to update NextHeadNo value based on next value on HeadNo.

So as per the data Row-3 should get update AX ➪ AD.

I have around 1000 records to update for single time.

I hope anyone can help me with this.

Upvotes: 1

Views: 220

Answers (1)

PSK
PSK

Reputation: 17943

You can try like following using a subquery.

SELECT *, 
       (SELECT TOP 1 headno 
        FROM   @table T2 
        WHERE  T2.headno > T1.headno 
        ORDER  BY headno) NextHeadNo 
FROM   @table T1 
ORDER  BY headno

Online Demo

To update, you can try like following.

UPDATE T1 SET NextHeadNo= 
       (SELECT TOP 1 headno 
        FROM   @table T2 
        WHERE  T2.headno > T1.headno 
        ORDER  BY headno)  
FROM   @table T1

Edit:

but in last row getting NULL, So i want to give its reference to first row. how can i do that?

I suggest you write a seperate update statement instead of writing CASE WHEN like following.

update t
set t.NextHeadNo =
 (
  select top 1 HeadNo from @table order by HeadNo
 )
from @table t
where t.NextHeadNo is null

Upvotes: 1

Related Questions