CODEWITHSUNDEEP
sqloracle-database
D. Mika
D. Mika

Reputation: 2798

SQL: Get next date value per row

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?

Upvotes: 3

Views: 10109

Answers (5)

Alex Poole
Alex Poole

Reputation: 191235

You could use an analytic function with a windowing clause. lead() doesn't support a windowing clause, so you need use one that does like min() or first_value():

FIRST_VALUE ("Date")
  OVER (ORDER BY "Date" RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

The default windowing clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which would give all your rows the same value of 2000-01-01 and using a ROWS window would run into the same problem you're having with lead() with duplicate dates (ID 2 would still get 2000-02-01; and ID 4 would get 2000-03-01 instead of null if you you used ROWS BETWEEN CURRENT ROW... rather than 1 FOLLOWING).

Demo using this range:

with t (ID, "Date") as (
  select 1, date '2000-01-01' from dual
  union all select 2, date '2000-02-01' from dual
  union all select 3, date '2000-02-01' from dual
  union all select 4, date '2000-03-01' from dual
)
select id, "Date", FIRST_VALUE ("Date") OVER (ORDER BY "Date"
  RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS next_date
FROM t;

        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

Only rows where the date value is higher than the current row are considered. And this still only has to hit the table once.

(I've put "Date" in double-quotes because date is a reserved word; from your sample data it looks like a quoted identifier, but it isn't quoted in your query, so it's probably just got a more sensible name really...)

Upvotes: 6

Ychdziu
Ychdziu

Reputation: 445

Here is another approach, without "distinct":

select
  ted.id,
  ted.date_col,
  (select
    min(ted2.date_col)
  from
    test_date_v ted2
  where
    ted2.id != ted.id and
    ted2.date_col > ted.date_col) next_date_col
from
  test_date_v ted;

Upvotes: 1

D. Mika
D. Mika

Reputation: 2798

To answer my own question. ;-) (Just to show another option to people stumbling across this post)

Another solution would be using a subselect:

SELECT t.id,
       t.date,
       (SELECT MIN (t.date)
          FROM t t2
         WHERE t2.date > t.date)
          AS next_date
  FROM t;

Upvotes: 3

Samanvitha - user2519049
Samanvitha - user2519049

Reputation: 384

select * , (select min(t2.date) from table t2 where t2.date > t1.date)
from Table t1

Above code is in sql server

Upvotes: 6

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

One approach would be to create a CTE containing the distinct dates and their immediate lead values. Then, join this CTE to your original table on the date to get the final result.

WITH cte AS (
    SELECT t.date,
           LEAD(t.date, 1) OVER (ORDER BY t.date NULLS LAST) AS next_date
    FROM (SELECT DISTINCT date FROM yourTable) t
)
SELECT
    t1.ID,
    t1.date,
    t2.next_date
FROM yourTable t1
INNER JOIN cte t2
    ON t1.date = t2.date

Upvotes: 1

Related Questions