I would need some help with below case:
My data contains dates of order arrival in one column.
I want to add second column what would use date from one row below:
Add a comment
|
1 Answer
Use lead():
select t.dept, t.date as arrival, t.next_arrival
from (select t.*, lead(date) over (partition by dept order by date) as next_arrival
from t
) t
where t.next_arrival is not null;
If you are happy with all rows in the result set and the next_arrival being NULL, then you don't need the subquery.
3 Comments
marcin2x4
I added a rule for NULL:
IFNULL(lead(date) over (partition by dept order by date), '9999-12-31') as next_arrival
Gordon Linoff
@marcin2x4 . . . That has nothing to do with your question, which does not include such a default value. But it is a perfectly legitimate thing to do -- except I would recommend the three-argument form of
LEAD() instead.marcin2x4
Yes, I forgot to post this addition to my question.
lead(date,1,'999-12-31') is indeed better ;)