Skip to content
Advertisement

Is there a way to group values getting only the ones that verifies certain condition?

I’m trying to write a query in Oracle SQL that aggregates values by some ids, where I have the following table as an input:

ID SOME_DATE RANK_POSITION
301 20211201 1
301 20211202 2
301 20211203 3
649 20211201 1
649 20211202 2
649 20211206 3
649 20211208 4
649 20211211 5
758 20211212 1
758 20211222 2

And y want to obtain something like this:

ID FIRST_IN_RANK_DATE SECOND_IN_RANK_DATE
301 01/12/2021 02/12/2021
649 01/12/2021 02/12/2021
758 12/12/2021 22/12/2021

Where FIRST_IN_RANK_DATE, is the date that is the first in the RANK_POSITION for the ID, and SECOND_IN_RANK_DATE is the date that is second in RANK_POSITION for the specific ID.

Advertisement

Answer

You can use conditional aggregation:

SELECT id,
       MAX(CASE rank_position WHEN 1 THEN some_date END) AS first_in_rank_date,
       MAX(CASE rank_position WHEN 2 THEN some_date END) AS second_in_rank_date
FROM   table_name
GROUP BY id

Or PIVOT:

SELECT *
FROM   table_name
PIVOT (
  MAX(some_date)
  FOR rank_position IN (
    1 AS first_in_rank_date,
    2 AS second_in_rank_date
  )
)

Or, from Oracle 12, MATCH_RECOGNIZE:

SELECT *
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY rank_position
  MEASURES
    rank1.some_date AS first_in_rank_date,
    rank2.some_date AS second_in_rank_date
  PATTERN ( ^ rank1 rank2 )
  DEFINE
    rank1 AS rank_position = 1,
    rank2 AS rank_position = 2
)

Which, for the sample data:

CREATE TABLE table_name (ID, SOME_DATE, RANK_POSITION) AS
SELECT 301, DATE '2021-12-01', 1 FROM DUAL UNION ALL
SELECT 301, DATE '2021-12-02', 2 FROM DUAL UNION ALL
SELECT 301, DATE '2021-12-03', 3 FROM DUAL UNION ALL
SELECT 649, DATE '2021-12-01', 1 FROM DUAL UNION ALL
SELECT 649, DATE '2021-12-02', 2 FROM DUAL UNION ALL
SELECT 649, DATE '2021-12-06', 3 FROM DUAL UNION ALL
SELECT 649, DATE '2021-12-08', 4 FROM DUAL UNION ALL
SELECT 649, DATE '2021-12-11', 5 FROM DUAL UNION ALL
SELECT 758, DATE '2021-12-12', 1 FROM DUAL UNION ALL
SELECT 758, DATE '2021-12-22', 2 FROM DUAL;

All output:

ID FIRST_IN_RANK_DATE SECOND_IN_RANK_DATE
301 2021-12-01 00:00:00 2021-12-02 00:00:00
649 2021-12-01 00:00:00 2021-12-02 00:00:00
758 2021-12-12 00:00:00 2021-12-22 00:00:00

db<>fiddle here

Advertisement