CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-groupwindow-functions
Cesc
Cesc

Reputation: 304

Last 6 rows for only one group

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?

Upvotes: 1

Views: 50

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65288

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.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

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;

Upvotes: 1

Related Questions