Oracle 18c

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

CREATE TABLE table_name ( id, name, ranking, start_date, end_date ) AS
SELECT 1001, 'Jones', 'Lieutenant', DATE '2000-03-20', DATE '2002-08-15' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Lieutenant', DATE '2002-08-16', DATE '2003-03-18' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Lieutenant', DATE '2003-03-19', DATE '2004-06-01' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Lieutenant', DATE '2004-06-02', DATE '2004-10-01' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Captain',    DATE '2004-10-02', DATE '2005-04-20' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Captain',    DATE '2005-04-21', DATE '2007-02-20' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Major',      DATE '2007-02-21', DATE '2008-10-22' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Major',      DATE '2008-10-23', DATE '2010-01-26' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Captain',    DATE '2010-01-27', DATE '2013-11-25' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Captain',    DATE '2013-11-26', DATE '2014-05-11' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'Major',      DATE '2014-05-12', DATE '2016-04-22' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'General',    DATE '2016-04-23', DATE '2020-10-10' FROM DUAL UNION ALL
SELECT 1001, 'Jones', 'General',    DATE '2020-10-11', DATE '2020-11-30' FROM DUAL;
13 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT *
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY start_date, end_date
  MEASURES
    FIRST( name )       AS name,
    FIRST( ranking )    AS ranking,
    FIRST( start_date ) AS start_date,
    LAST( end_Date )    AS end_Date
  PATTERN ( same_rank+ )
  DEFINE same_rank AS FIRST( ranking ) = ranking
)


ID
NAME
RANKING
START_DATE
END_DATE


1001
Jones
Lieutenant
2000-03-20 00:00:00
2004-10-01 00:00:00

1001
Jones
Captain
2004-10-02 00:00:00
2007-02-20 00:00:00

1001
Jones
Major
2007-02-21 00:00:00
2010-01-26 00:00:00

1001
Jones
Captain
2010-01-27 00:00:00
2014-05-11 00:00:00

1001
Jones
Major
2014-05-12 00:00:00
2016-04-22 00:00:00

1001
Jones
General
2016-04-23 00:00:00
2020-11-30 00:00:00

ID	NAME	RANKING	START_DATE	END_DATE
1001	Jones	Lieutenant	2000-03-20 00:00:00	2004-10-01 00:00:00
1001	Jones	Captain	2004-10-02 00:00:00	2007-02-20 00:00:00
1001	Jones	Major	2007-02-21 00:00:00	2010-01-26 00:00:00
1001	Jones	Captain	2010-01-27 00:00:00	2014-05-11 00:00:00
1001	Jones	Major	2014-05-12 00:00:00	2016-04-22 00:00:00
1001	Jones	General	2016-04-23 00:00:00	2020-11-30 00:00:00