Oracle Query to find the Nth oldest visit of a person
I think this will work: Ran test with this data:
create table test (PersonID number, VisitedOn date);
insert into test values(1,'01-JAN-2000');
insert into test values(1,'01-JAN-2001');
insert into test values(1,'01-JAN-2002');
insert into test values(1,'01-JAN-2003');
insert into test values(2,'01-JAN-2000');
insert into test values(2,'01-JAN-2001');
select personid, visitedon
from (
select personid,
visitedon,
row_number() over ( partition by personid order by visitedon ) rn
from test
)
where rn=5
What this does is use an analytic function to assign a row number to each set of records partitioned by the person id, then pick the Nth row from each partitioned group, where the rows in each group are sorted by date. If you run the inner query by itself, you will see where the row_number is assigned:
PERSONID VISITEDON RN
1 01-JAN-00 1
1 01-JAN-01 2
1 01-JAN-02 3
1 01-JAN-03 4
2 01-JAN-00 1
2 01-JAN-01 2