Oracle 11g Release 2

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

CREATE TABLE subset(
   Person      VARCHAR(4) NOT NULL
  ,PersonID    INTEGER  NOT NULL
  ,PhoneNumber INTEGER  NOT NULL
  ,Address1    VARCHAR(17) NOT NULL
  ,ZipCode     INTEGER  NOT NULL
  ,StartDate   DATE  NOT NULL
  ,EndDate     DATE  NOT NULL
);

INSERT INTO subset(Person,PersonID,PhoneNumber,Address1,ZipCode,StartDate,EndDate) VALUES ('Jane',1,8792029484,'some-address-1',12345,TO_DATE('03/05/2016', 'mm/dd/yyyy'), to_date('11/9/2018', 'mm/dd/yyyy'));
1 rows affected

INSERT INTO subset(Person,PersonID,PhoneNumber,Address1,ZipCode,StartDate,EndDate) VALUES ('Jane',1,8792029484,'some-address-2',34455,to_date('11/10/2018', 'mm/dd/yyyy'),to_date('12/31/9999', 'mm/dd/yyyy'));
1 rows affected

INSERT INTO subset(Person,PersonID,PhoneNumber,Address1,ZipCode,StartDate,EndDate) VALUES ('Jane',1,8792029484,'some-address-2',34455,to_date('11/10/2018', 'mm/dd/yyyy'),to_date('12/7/2018', 'mm/dd/yyyy'));
1 rows affected

INSERT INTO subset(Person,PersonID,PhoneNumber,Address1,ZipCode,StartDate,EndDate) VALUES ('Jane',1,8792029484,'some-address-2',34455,to_date('12/8/2018', 'mm/dd/yyyy'),to_date('12/31/9999', 'mm/dd/yyyy'));
1 rows affected

INSERT INTO subset(Person,PersonID,PhoneNumber,Address1,ZipCode,StartDate,EndDate) VALUES ('John',2,9808845768,'another-address-1',68687,to_date('1/1/2013', 'mm/dd/yyyy'),to_date('11/9/2018', 'mm/dd/yyyy'));
1 rows affected

INSERT INTO subset(Person,PersonID,PhoneNumber,Address1,ZipCode,StartDate,EndDate) VALUES ('John',2,9808845768,'another-address-2',89384,to_date('11/10/2018', 'mm/dd/yyyy'),to_date('12/31/9999', 'mm/dd/yyyy'))
1 rows affected

INSERT INTO subset(Person,PersonID,PhoneNumber,Address1,ZipCode,StartDate,EndDate) VALUES ('John',2,9808845768,'another-address-2',89384,to_date('11/10/2018', 'mm/dd/yyyy'),to_date('12/7/2018', 'mm/dd/yyyy'));
1 rows affected

INSERT INTO subset(Person,PersonID,PhoneNumber,Address1,ZipCode,StartDate,EndDate) VALUES ('John',2,9808845768,'another-address-2',89384,to_date('12/8/2018', 'mm/dd/yyyy'),to_date('12/31/9999', 'mm/dd/yyyy'));
1 rows affected

SELECT person, personid, phonenumber, address1, zipcode, startdate, enddate
FROM (
    SELECT s.*, ROW_NUMBER() OVER(PARTITION BY personID, address1, startdate ORDER BY enddate) rn
    FROM subset s
) x WHERE rn = 1


PERSON
PERSONID
PHONENUMBER
ADDRESS1
ZIPCODE
STARTDATE
ENDDATE


Jane
1
8792029484
some-address-1
12345
05-MAR-16
09-NOV-18

Jane
1
8792029484
some-address-2
34455
10-NOV-18
07-DEC-18

Jane
1
8792029484
some-address-2
34455
08-DEC-18
31-DEC-99

John
2
9808845768
another-address-1
68687
01-JAN-13
09-NOV-18

John
2
9808845768
another-address-2
89384
10-NOV-18
07-DEC-18

John
2
9808845768
another-address-2
89384
08-DEC-18
31-DEC-99

PERSON	PERSONID	PHONENUMBER	ADDRESS1	ZIPCODE	STARTDATE	ENDDATE
Jane	1	8792029484	some-address-1	12345	05-MAR-16	09-NOV-18
Jane	1	8792029484	some-address-2	34455	10-NOV-18	07-DEC-18
Jane	1	8792029484	some-address-2	34455	08-DEC-18	31-DEC-99
John	2	9808845768	another-address-1	68687	01-JAN-13	09-NOV-18
John	2	9808845768	another-address-2	89384	10-NOV-18	07-DEC-18
John	2	9808845768	another-address-2	89384	08-DEC-18	31-DEC-99