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.

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