Sql Statement Using Where From A Group Or Rank

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

Solution 1:

The answer is already in your title, partition by SalesRep and AccountID and Rank by Total Contact Value.

A SQL Server solution will look like:

DECLARE@minimumValuedecimal(20,2) =10000DECLARE@numberOfAccountsint=35DECLARE@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 (PARTITIONBY [Sales Rep] ORDERBY [Total Contract Value] DESC) AS [Rank]
  FROM [Sales]
  WHERE
    [Total Contract Value] >=@minimumValueAND [Date] >@fromAND [Date] <@till
) AS [sub]
WHERE [sub].[Rank] <=@numberOfAccountsORDERBY 
  [Sales Rep] ASC,
  [Rank] ASC

Here is a (simple) Sql Fiddle.

Solution 2:

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

select ss.*from (select ss.*, row_number() over (partitionby salesrep orderby ContractValue desc) as seqnum
      from snapshot ss
      where TotalContractValue >=10000anddatebetween'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.