CODEWITHSUNDEEP
sqlsql-server
Arvind
Arvind

Reputation: 6474

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

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...

Upvotes: 4

Views: 6159

Answers (3)

Elias Hossain
Elias Hossain

Reputation: 4469

Just going straight forward:

SELECT TOP 3 CompanyID, Year, Sales
FROM (SELECT CompanyID, Year, Sales, 
             ROW_NUMBER() OVER(ORDER BY CompanyID ASC,Sales DESC) AS RowNumber 
      FROM TargetTbl) AS TempTable
ORDER BY RowNumber ASC

EDIT: replace 3 with your desired value of n(top number of rows).

Upvotes: 0

Adam Wenger
Adam Wenger

Reputation: 17540

If you are SQL 2005+, the CTE approach will work:

WITH salesWithRank AS
(
   SELECT CompanyID, Year, Sales
      , RANK() OVER (PARTITION BY CompanyId ORDER BY Sales DESC) AS RowNumber
   FROM SalesData
)
SELECT CompanyID, Year, Sales
FROM salesWithRank AS s
WHERE RowNumber <= 10 -- Top 10 sales for each CompanyId, any year
ORDER BY CompanyId ASC, Sales DESC

Note, if you have CompanyName in the table, you could use that to Order by as well.

Upvotes: 3

Adam Robinson
Adam Robinson

Reputation: 185643

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.

Upvotes: 5

Related Questions