SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select Top n Based on Multiple Variables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

joe_s
Starting Member

1 Posts

Posted - 03/31/2014 :  14:55:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 03/31/2014 :  15:29:12  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000