Select Top n Based on Multiple Variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select Top n Based on Multiple Variables

Author

Topic

joe_s
Starting Member

1 Post

Posted - 2014-03-31 : 14:55:57

Hi,

Pretty new to SQL here. I have a table that includes Region (1-10), Month, Member ID, and multiple transactions per member (which will be sum of paid). I'd like to extract the top 5 sum of paid amounts....by region, by month, by member. So the resulting query will have 50 results per month (top 5 for each region each month). I've tried in vain to mimic some code I've found online, so any help/input/advice would be very, very much appreciated.

Thank you all.

Joe

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-31 : 15:29:12

You can use the row_number function with appropriate partitioning and ordering clauses - for example:

;WITH cte AS
(
	SELECT *, 
		ROW_NUMBER() OVER (PARTITION BY region, MONTH, member ORDER BY amount DESC) AS RN
	FROM
		YourTable
) SELECT * FROM cte 
WHERE RN <= 5;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources