Consider the below
Id Nums
1 10
2 20
3 30
4 40
5 50
The expected output
Id CurrentValue PreviousValue
1 10 Null
2 20 10
3 30 20
4 40 30
5 50 40
I am trying with the below but no luck
;With Cte(Id,CurrValue,PrevValue) As
(
Select
Id
,CurrentValue = Nums
,PreviousValue = Null
From @t Where Id = 1
Union All
Select
t.Id
,c.CurrValue
,c.PrevValue
From Cte c
Join @t t On c.Id <= t.Id + 1
)
Select *
From Cte
Help needed
This assumes increasing ID values and will deal with gaps in ID
SELECT
ID,
This.Number AS CurrentValue,
Prev2.Number AS PreviousValue
FROM
myTable This
OUTER APPLY
(
SELECT TOP 1
Number
FROM
myTable Prev
WHERE
Prev.ID < This.ID -- change to number if you want
ORDER BY
Prev.ID DESC
) Prev2;
OR
WITH CTE
AS (SELECT ID,
Number,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM Mytable)
SELECT ID,
This.Number AS CurrentValue,
Prev.Number AS PreviousValue
FROM CTE This
LEFT JOIN CTE Prev
ON Prev.rn + 1 = This.rn;
And for SQL Server 2012
SELECT
ID,
Number AS CurrentValue,
LAG(Number) OVER (ORDER BY ID) AS PreviousValue
FROM
MyTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With