SQL using WHERE from a group or RANK

Question

I have a sales snapshot containing about 35,000 rows. Let the columns be called:

Sales Rep | Account ID | Total Contract Value | Date 

I need to group everything by Sales Rep , and then from there select Sales Rep top 35 accounts based on Total Contract Value , where Total Contract Value > = $ 10,000 per month (date) January 2013 .

So, for example, let's say John Doe has had 294 accounts in this table since January, I just want to see his top 35 accounts> = $ 10,000, the same for Jane Doe, etc. etc. It is very important that the request is as efficient in using resources as possible.

Thoughts?

Answer 1

For this you want to use a function called row_number() :

 select ss.* from (select ss.*, row_number() over (partition by salesrep order by ContractValue desc) as seqnum from snapshot ss where TotalContractValue >= 10000 and date between '2013-01-01' and '2013-01-31' ) ss where seqnum <= 35 

You do not specify the database you are using. In databases that do not have row_number() , there are alternatives that are less efficient.

Answer 2

The answer is already in your heading, the SalesRep and AccountID and the Total Contact Value rank.

Decision

A SQL Server will look like this:

 DECLARE @minimumValue decimal(20,2) = 10000 DECLARE @numberOfAccounts int = 35 DECLARE @from datetime = '1/1/2013' DECLARE @till datetime = DATEADD(MONTH, 1, @from) SELECT [sub].[Sales Rep], [sub].[Rank], [sub].[Account ID], [sub].[Total Contract Value] FROM ( SELECT [Sales Rep], [Account ID], [Total Contract Value], DENSE_RANK() OVER (PARTITION BY [Sales Rep] ORDER BY [Total Contract Value] DESC) AS [Rank] FROM [Sales] WHERE [Total Contract Value] >= @minimumValue AND [Date] > @from AND [Date] < @till ) AS [sub] WHERE [sub].[Rank] <= @numberOfAccounts ORDER BY [Sales Rep] ASC, [Rank] ASC 

Here is a (simple) Sql Fiddle .