CODEWITHSUNDEEP
sqlsql-servert-sql
HolyUnicow
HolyUnicow

Reputation: 29

SQL top five records per month and new column displaying this rank

I have a simple query - but I need it to only return the top n number of records for that month based on Money, and also display a new column with this rank in.... so highest is 1 etc.

SELECT Purchase.Money  
       , Employee.FName
       , Employee.LName      
       , DateName(month, Purchase.Date) AS MonthName
       , Year(Purchase.Date) AS Year
       , Purchase.Date
FROM DB.Purchase.Purchase       
INNER JOIN DB.Employee.Employee        
ON Purchase.ID=Person.ID        
WHERE Purchase.Date BETWEEN '20001-01-01' AND '2002-01-01'            
group by 
    Employee.FirstName      
    , Employee.LastName
    , Employee.ID
    , Purchase.TotalDue 
    , DateName(month, Purchase.Date)
    , Year(Purchase.Date)
    , Purchase.Date
ORDER BY Year(Purchase.Date),
         DateName(month,Purchase.Date), 
         Purchase.Money DESC

Upvotes: 0

Views: 1535

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Use row_number():

with cte as (<your query here>)
select t.*
from (select t.*,
             row_number() over (partition by MonthName, year order by money desc) as seqnum
      from cte
     ) t
where seqnum <= 5;

Upvotes: 2

Related Questions