CODEWITHSUNDEEP
sqlsql-server
user1500633
user1500633

Reputation:

Referencing a value from the previous row

Can anyone help me to write query in Sql Server 2014 for the following condition: I need to add the first row CurrentKM value in second row LastKM column like that it has to go.

VNo  CurrKM   LastKM
1    15000    0
2    16000    15000
3    17000    16000

Upvotes: 2

Views: 151

Answers (2)

mohan111
mohan111

Reputation: 8865

declare @t table (id int,currkm int)
insert into @t (id,currkm)values (1,15000),(2,16000),(3,17000)
;with cte as(
select distinct id,currkm,ROW_NUMBER()OVER( order by id desc,currkm desc)R from @t)
select c.id,c.currkm,cc.currkm from cte c
LEFT JOIN cte cc
on cc.R - 1 = c.R
ORDER BY c.id

Upvotes: 0

Christian Barron
Christian Barron

Reputation: 2755

Try using LAG:

Select VNo,CurrKM, LAG(CurrKM,1,0) over (order by VNo asc) LastKM
From Table

Upvotes: 3

Related Questions