SQL: Get next date value per row

Viewed 7564

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?

5 Answers
Related