CODEWITHSUNDEEP
sqloracle-database
HuJohner
HuJohner

Reputation: 41

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

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

Upvotes: 3

Views: 112

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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).

Upvotes: 2

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

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

Upvotes: 0

Popeye
Popeye

Reputation: 35930

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;

Upvotes: 0

forpas
forpas

Reputation: 164184

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522636

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

Upvotes: 0

Related Questions