CODEWITHSUNDEEP
sql-servert-sqlmax
RockScience
RockScience

Reputation: 18580

SQL Server: select TOP2 element by group

I have a table with following columns: item (varchar) date (date) revision (datetime) value (numeric)

I have this SQL statement that finds the last revision for each item:

select item, max(revision) as 'last_revision' 
from [mytable] 
group by item 
ORDER BY last_revision ASC

However I am looking to get the last 2 revisions for each item:

item|last_revision|previous_revision

I have tried this which fails miserably

select TOP 2 item, revision as 'last_revision' 
from [myTable] 
group by item 
ORDER BY last_revision ASC

Column 'myTable.revision' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 103

Answers (3)

uzi
uzi

Reputation: 4146

Try this queries. Both of them should work

select
    item, revision
from (
    select 
        item, revision
        , rn = row_number() over (partition by item order by revision desc)
    from [mytable] 
    group by item , revision
) t
where rn <= 2

Or

select
    distinct item, revision
from (
    select 
        item, revision
        , rn = dense_rank() over (partition by item order by revision desc)
    from [mytable]
) t
where rn <= 2

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You appears to want top (4) with ties

select top (4) with ties item, revision  
from mytable t
order by row_number() over (partition by item order by revision desc)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Use ROW_NUMBER:

SELECT item, revision AS last_revision
FROM
(
    SELECT item, revision,
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY revision) rn
    FROM [myTable]
) t
WHERE rn <= 2;

SQL Server's TOP operator just limits the number of records returned for the entire result set, not for each group in your aggregation. You really should use analytic functions here.

Upvotes: 0

Related Questions