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

Question

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.

You can join with same table (self join) on tb1.id = tb2.id+1..... — DatabaseCoder, Feb 14 '19 at 05:56
Do you know on the basis of which column you want to pick next value? — DatabaseCoder, Feb 14 '19 at 05:58
You need to have a column to order on the basis of which you will pick the next record. — PSK, Feb 14 '19 at 05:59
@SQLCoder: as shown in sample, take next value of `ORDER BY Head_No` — Irfan, Feb 14 '19 at 06:00

PSK · Accepted Answer · 2019-02-14T06:38:05.620

1

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

edited Feb 14 '19 at 06:38

answered Feb 14 '19 at 06:01

PSK

17,547
5
32
43

thanks, it's worked. but in last row getting NULL, So i want to give its reference to first row. how can i do that? – Irfan Feb 14 '19 at 06:22
You an write a separate update or put a case expression in the same quey – PSK Feb 14 '19 at 06:27
@jap_jap, i have updated the answer, you can have a look. – PSK Feb 14 '19 at 06:38

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

1 Answers1