CODEWITHSUNDEEP
sqlgoogle-bigquery
HilaD
HilaD

Reputation: 881

Get next value (if exist) per key, value in BigQuery

I have got this king of table:

id  number
A.    4
A.    7
A.    8
B.    10
C.    1
C.    9

AND I need to add to each row the next number per id using sql, like this:

id  number. next_number
A.    4        7
A.    7.       8
A.    8       -/8 (null or the same number if its last)
B.    10.     -/10
C.    1        9
C.    9       -/9

Can you help?

Upvotes: 0

Views: 1453

Answers (1)

Cylldby
Cylldby

Reputation: 1978

This query should do what you want:

SELECT
  id,
  number,
  LEAD(number) OVER(PARTITION BY id ORDER BY id) AS next_number
FROM
  `your_table`

You get from this table:

enter image description here

This table:

enter image description here

Upvotes: 1

Related Questions