Fetch record from table?

I have table in which data is like this

Mapping Relationship

1 task_id is mapped to multiple task_detail_id (1 to Many)

Task_detail_id  Task_id    Creation_date
1                20         2020-05-02 20:28:23.354
2                21         2020-05-02 20:28:23.354
3                22         2020-05-02 19:28:23.354
4                22         2020-05-02 18:28:23.354
5                22         2020-05-02 17:28:23.354
6                22         2020-05-02 16:28:23.354
7                22         2020-05-02 15:28:23.354
8                23         2020-05-02 10:28:23.354
9                24         2020-05-02 09:28:23.354
10               24         2020-05-02 08:28:23.354
11               24         2020-05-02 07:28:23.354

What I want is to traverse the table and fetch record as if same task_id exist more than 2 times then fetch top 2 (latest) records for that task_id

Sample Output

Task_detail_id  Task_id         Creation_date
1                 20            2020-05-02 20:28:23.354
2                 21            2020-05-02 20:28:23.354
3                 22            2020-05-02 19:28:23.354
4                 22            2020-05-02 18:28:23.354
8                 23            2020-05-02 10:28:23.354
9                 24            2020-05-02 09:28:23.354
10                24            2020-05-02 08:28:23.354

Answer

This is typically done with a window function:

select task_detail_id, task_id, creation_date
from (
  select task_detail_id, task_id, creation_date, 
         row_number() over (partition by task_id order by creation_date desc) as rn
  from data
) t
where rn <= 2
order by task_detail_id, task_id;

Online example

Advertisement

Answer