SQL query selecting non unique entries ordering by unique-CodePudding

My SQL server sample table have two columns ID and Date where ID is not unique.

ID  Date
1   1/1/2022
2   1/1/2021
3   1/1/2020
1   1/1/1999

I want to select the most recent n entries with unique IDs together with all entries matching those IDs so for example

for n=1:
1   1/1/2022
1   1/1/1999

and

for n=2:
1   1/1/2022
1   1/1/1999
2   1/1/2021

What would be the most efficient way to achieve this ?

I think I may need a self join but not sure how to proceed.

CodePudding user response：

You may use the RANK() function to find the top N most recent IDs, then use that result as a subquery. For example, your n=2 query would look like this:

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY Date) rnk
    FROM yourTable
)

SELECT ID, Date
FROM yourTable
WHERE ID IN (SELECT ID FROM cte WHERE rnk <= 2)
ORDER BY ID, Date DESC;

CodePudding user response：

You can use ROW_NUMBER window function to order and find the best of each id, then use Top N with ORDER BY to filter for the TopN ids, and then self join to get all entries for those ids:

select *
from Tbl T
     inner join
     (  select top 2 *   --<<< This is 'N'
        from
           (
          select ID, Date
          from (
                select id, Date, row_number() over (partition by id order by Date desc) as RN1
                from Tbl
               ) S1
          where S1.RN1=1
          ) as BestOfEachId
        order by Date desc
    ) TopN
    on T.id=TopN.ID