SQL query to find top n values for a column for a specific type of data set

Question

I have with me some data - Sales data for different companies for different years. So I have company ID, Year and Sales of that company (for that year).

What I want is to obtain the TOP n values of sales, and corresponding company ID and Year, for each company that has data.

There are other queries in SO but they are for straight forward TOP n values for a single column (without conditions like the one required here).

Any help would be appreciated...

Answer 1

You probably need something like this:

select
    CompanyName,
    Year,
    Sales

from (
    select *, 
    row_number() over (partition by CompanyName order by Sales desc) as RowNbr 

    from data) src

where RowNbr <= 5

order by CompanyName, Sales desc

Just replace 5 with whatever number you like.