I have the following Oracle table
| PersonID | VisitedOn |
|---|---|
| 1 | 1/1/2017 |
| 1 | 1/1/2018 |
| 1 | 1/1/2019 |
| 1 | 1/1/2020 |
| 1 | 2/1/2020 |
| 1 | 3/1/2020 |
| 1 | 5/1/2021 |
| 1 | 6/1/2022 |
| 2 | 1/1/2015 |
| 2 | 1/1/2017 |
| 2 | 1/1/2018 |
| 2 | 1/1/2019 |
| 2 | 1/1/2020 |
| 2 | 2/1/2020 |
| 3 | 1/1/2017 |
| 3 | 1/1/2018 |
| 3 | 1/1/2019 |
| 3 | 1/1/2020 |
| 3 | 2/1/2020 |
| 3 | 3/1/2020 |
| 3 | 5/1/2021 |
I try to write a query to return the Nth oldest visit of each person. For instance if I want to return the 5th oldest visit (N=5) the result would be
| PersonID | VisitDate |
|---|---|
| 1 | 1/1/2020 |
| 2 | 1/1/2017 |
| 3 | 1/1/2019 |
CodePudding user response：
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