Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;
10 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;


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


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


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


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
)


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

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