SQL query to find the top n values ​​for a column for a particular dataset type

Question

I have some data - Sales data for different companies in different years. Therefore, I have the company ID , Year and Sales this company (for this year).

I want to get the sales values TOP n and the corresponding companies ID and Year for each company with data.

There are other queries in SO, but they are for direct TOP n values ​​for a single column (without conditions similar to those required here).

Any help would be appreciated ...

Answer 1

You will 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 quantity you like.

Answer 2

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 that if you have a computer_name in the table, you can use this to order as well.

Answer 3

Just go ahead:

 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 the desired n value (top number of lines).