Query to find the FIRST AND SECOND largest value from a group

Question

i have a query like this:

SELECT
 DATEPART(year,some_date),
 DATEPART(month,some_date),
 MAX(some_value) max_value
FROM
 some_table
GROUP BY
    DATEPART(year,some_date),
    DATEPART(month,some_date)

This returns a table with: year, month, the largest value for the month.

I would like to modify the query so that i could obtain: year, month, the largest value for the month, the second largest value for the month in each row.

It seems to me that the well-known solutions like "TOP 2", "NOT IN TOP 1" or a subselect won't work here.

(To be really specific - i am using SQL Server 2008.)

Answer 1

It seems to me that the question calls for a query that would return best, and second best in the same row for each month and year, like so:

month, year, best, second best
...
...

and not two rows for the same month and year containing best and second best value.

This is the solution that I came up with, so if anyone has a simpler way of achieving this, I would like to know.

with ranks as (
    select 
        year(entrydate) as [year], 
        month(entrydate) as [month], 
        views, 
        rank() over (partition by year(entrydate), month(entrydate) order by views desc) as [rank]
    from product
)
select 
    t1.year, 
    t1.month, 
    t1.views as [best], 
    t2.views as [second best]
from ranks t1
    inner join ranks t2
        on t1.year = t2.year
        and t1.month = t2.month
        and t1.rank = 1
        and t2.rank = 2

EDIT: Just out of curiosity I did a bit more testing and ended up with a simpler variation on the Stephanie Page's answer that doesn't use an aditional subquery. And I changed the rank() function to row_number() as it doesn't work when two max values are the same.

with ranks as (
    select 
        year(entrydate) as [year], 
        month(entrydate) as [month], 
        views, 
        row_number() over (partition by year(entrydate), month(entrydate) order by views desc) as [rank]
    from product
)
select 
    t1.year, 
    t1.month, 
    max(case when t1.rank = 1 then t1.views else 0 end) as [best], 
    max(case when t1.rank = 2 then t1.views else 0 end) as [second best]
from 
    ranks t1
where
    t1.rank in (1,2)
group by
    t1.year, t1.month

Answer 2

RANK() is maybe the thing you are looking for...

http://msdn.microsoft.com/en-us/library/ms176102.aspx