我有一张表，比方说emp_sal，我想要找出员工的3rd record，基于descending order的change_date.
但问题是对于很少的员工，只有不到三条记录，对于这样的员工，我需要第二条(如果表中有两行该员工)或第一条(如果表中只有一条记录)记录.
emp_sal table with data:个
| empid | change_date | salary |
|---|---|---|
| 1 | 2023-01-01 | 1000 |
| 1 | 2023-02-01 | 1400 |
| 1 | 2023-03-01 | 1450 |
| 1 | 2023-04-01 | 1500 |
| 2 | 2023-11-01 | 2500 |
| 2 | 2023-12-01 | 2400 |
| 2 | 2023-12-15 | 2200 |
| 3 | 2023-05-01 | 500 |
| 3 | 2023-06-01 | 640 |
| 4 | 2023-10-01 | 3000 |
我试着使用ROW_NUMBER，但无法获得少于3条记录的记录.
select * from
(select e.*, row_number() over (partition by empid order by change_date desc) as rn
from emp_sal e)
where rn = 3; -- what other condition is needed here, I am not sure
Expected output:个
| empid | change_date | salary |
|---|---|---|
| 1 | 2023-02-01 | 1400 |
| 2 | 2023-11-01 | 2500 |
| 3 | 2023-05-01 | 500 |
| 4 | 2023-10-01 | 3000 |
我们将非常感谢您的帮助.如果需要更多细节，请让我知道.