SQL statement using WHERE from a GROUP or RANK

Question

I have a sales snapshot with about 35,000 rows. Let's call the columns:

Sales Rep | Account ID | Total Contract Value | Date

I need to group everything by Sales Rep and then from there, select that Sales Rep's top 35 accounts based off of Total Contract Value where the Total Contract Value is >= $10,000 for the Month (Date) of January 2013.

So for example, say John Doe had 294 accounts in this table from January, I only want to see his top 35 accounts >= $10,000 , same for Jane Doe, etc. etc. It's very important that the query be as efficient in it's resource usage 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 don't specify the database you are using. In databases that don't have row_number(), there are alternatives that are less efficient.