Last 6 rows for only one group

Question

i have table:

label   added
1-1     2020-11-09 08:22:37.000
x1      2020-11-09 07:22:37.000
x3      2020-11-09 07:25:43.000
x6      2020-11-09 05:42:37.000
x1      2020-11-07 04:22:37.000
1-1     2020-11-09 08:22:37.000
1-1     2020-11-09 08:22:37.000
1-1     2020-11-06 08:22:37.000
1-1     2020-11-03 08:22:37.000
1-1     2020-11-02 08:22:37.000
1-1     2020-11-01 05:22:37.000
1-1     2020-10-09 01:52:37.000

I want to select all records, but for the label "1-1" only the last 6 newest. This will probably work with the rank function:

SQL * FROM (
    SELECT label, added
           , RANK() OVER (PARTITION by label order by added desc) as lastN
    FROM table
) t
WHERE (label = '1-1' AND lastN <= 6)

But I would like to know if it can be done differently? Maybe without a correlated subquery?

Answer 1

Your current rank query is pretty optimal, though you could also use TOP here with a union:

SELECT * FROM yourTable WHERE label <> '1-1'
UNION ALL
SELECT * FROM (
    SELECT TOP 6 *
    FROM yourTable
    WHERE label = '1-1'
    ORDER BY added DESC
) t;