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?

Since you can't use `rank` and `where rank = ` in same query it is the optimal way — Equinox, Nov 23 '20 at 07:36

score 1 · Accepted Answer · answered Nov 23 '20 at 07:37

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;

answered Nov 23 '20 at 07:37

Tim Biegeleisen

502,043
27
286
360

Barbaros Özhan · Answer 2 · 2020-11-23T08:56:55.707

0

You can use the current window function, in which PARTITION BY should also be removed , within the ORDER BY clause of the main query along with TOP 6 in order to make the query without a subquery

SELECT TOP 6 *
  FROM [table]
 WHERE label = '1-1'
 ORDER BY RANK() OVER (ORDER BY added DESC)

Demo

in this case label column within the ORDER BY clause is also redundant as being filtered out in the WHERE condition.

edited Nov 23 '20 at 08:56

answered Nov 23 '20 at 08:28

Barbaros Özhan

59,113
10
31
55

Last 6 rows for only one group

2 Answers2