input
id name date value
1 aa x v1
1 aa y v1
1 aa z v1
2 bb a v2
2 bb b v2
3 cc c v2
4 dd d v3
4 dd e v3
5 ee f v4
output
id name date value lead value
1 aa x v1 v2
1 aa y v1 v2
1 aa z v1 v2
2 bb a v2 v2
2 bb b v2 v2
3 cc c v2 v3
4 dd d v3 v4
4 dd e v3 v4
5 ee f v4 null
How to get lead value for a id? i.e for id 1 lead value is v2,for id 2 lead value again is v2(as id 3 value is v2), for id 3 lead value is v3 and so on..
You can use the below query to get the desired result:
with
CTE1(ID, VALUE) AS
(select distinct id,
value from table1
order by id
),
CTE2 AS
(
SELECT A.ID,
A.VALUE,
LEAD(A.VALUE) OVER(ORDER BY A.ID) LEAD_VALUE
FROM CTE1 A
)
SELECT A.*, B.LEAD_VALUE
FROM TABLE1 A, CTE2 B
WHERE A.ID = B.ID;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments