SQL: Select a row from a table with an additional column containing the next value of the column

user_13526455 0
Viewed 65

Let's assume I have a table "TABLE_A" in an Oracle database:

=======================
| id | key | date     |
=======================
| 0  | 1   | 1.1.2020 |
| 1  | 1   | 1.1.2021 |
=======================

I want to get a result like this:

===================================
| id | key | date     | next_date |
===================================
| 0  | 1   | 1.1.2020 | 1.1.2021  |
===================================

Note that I want the row with a certain key <ID> on a certain date <DATE> with another column that contains the next date in the database with the same key. However if there is no other date it should still give me the same row but with next_date empty.

Is there a simpler / better / more readable version than this?

SELECT a.*, next_date
FROM TABLE_A a,
     (SELECT key, date as next_date
      FROM TABLE_A
      WHERE key = <ID>
        AND date > <DATE>
        AND ROWNUM <= 1
      ORDER BY next_date asc) a2
WHERE key = <ID>
  AND date = <DATE>
  AND a2.key(+) = a.key
oracle sql
5 Answers

Although lead() is what you are describing, I think that a correlated subquery might be fastest:

select t.*,
       (select min(t2.date)
        from t t2
        where t2.key = t.key and t2.date > t.date
       ) as next_date
from t;

(You can add a filter for a particular key.)

In particular, this makes very efficient use of an index on (key, date).

user_11440350

I would use ROW_NUMBER here along with pivoting logic:

WITH cte AS (
    SELECT a.*, ROW_NUMBER() OVER (PARTITION BY "key" ORDER BY "date") rn
    FROM TABLE_A a
)

SELECT
    MIN(id) AS id,
    "key",
    MAX(CASE WHEN rn = 1 THEN "date" END) AS "date",
    MAX(CASE WHEN rn = 2 THEN "date" END) AS next_date
FROM cte
GROUP BY
    "key";

screen capture from demo link below

Demo

user_18632290

Use LEAD() window function:

SELECT t.*
FROM (
  SELECT t.*, LEAD(t."date") OVER (PARTITION BY t."key" ORDER BY t."date") NEXT_DATE
  FROM tablename t
  WHERE t."key" = 1  -- remove this line if you want results for all the keys
) t
WHERE t."date" = date '2020-01-01'

See the demo.
Results:

> id | key | date      | NEXT_DATE
> -: | --: | :-------- | :--------
>  0 |   1 | 01-JAN-20 | 01-JAN-21
user_104988280

Yet another option is to use the self join as follows:

SELECT T1.ID, T1.KEY, T1.DATE, 
       MIN(T2.DATE) AS NEXT_DATE
  FROM TABLE_A T1 LEFT JOIN TABLE_A T2
    ON T1.KEY = T2.KEY AND T2.DATE > T1.DATE
 WHERE T1.KEY = <KEY>
   AND T1.DATE = <DATE>
GROUP BY T1.ID, T1.KEY, T1.DATE;
user_115656290

If just one repetition for the key column value throughout the rows is the case, then use a simple aggregation :

SELECT MIN(ID) AS ID, key, MIN("date") AS "date", MAX("date") AS next_date
  FROM TABLE_A
 GROUP BY key

Otherwise(two many repetitions exist for the concerned key value and the next date strictly matters), then use LAG() function along with ROW_NUMBER() descendingly ordered by "date" column in order to pick the first returning row such as

WITH A AS
(
SELECT MIN(ID) OVER (PARTITION BY key) AS ID, 
       key, 
       MIN("date") OVER (PARTITION BY key) AS "date", 
       LAG("date") OVER (PARTITION BY key ORDER BY "date") AS next_date,
       ROW_NUMBER() OVER (PARTITION BY key ORDER BY "date" DESC) AS rn 
  FROM TABLE_A
)
SELECT id, key, "date", next_date
  FROM A
 WHERE rn = 1

Demo

user_58413060
Related