I've a table containing a date column.
ID | Date
----|-----------
1 | 2000-01-01
2 | 2000-02-01
3 | 2000-02-01
4 | 2000-03-01
I need a select that returns for each row, the ID, the Date and the smallest date (of all dates in the table) that is larger than the current date.
ID | Date | Next date
----+------------+------------
1 | 2000-01-01 | 2000-02-01
2 | 2000-02-01 | 2000-03-01
3 | 2000-02-01 | 2000-03-01
4 | 2000-03-01 | (NULL)
My first approach was
SELECT id, date, LEAD (date, 1) OVER (ORDER BY date NULLS LAST) AS next_date
FROM t
But this only works, if the values in column DATE are unique.
Any ideas?