Avatar of Ron Malmstead
Ron Malmstead
Flag for United States of America asked on

MSSQL Query: Help with sorting "Top Sales Agent" with lower [ID] as the tie breaker.

Need help sorting "Top Sales Agent" ... when there needs to be a tiebreaker..

The tie breaker is the agent who made their sale first, which is the lower [id] in the table.

Example desired output...(when there is one clear winner.)

[Agent Name],[Sale Count],[ID]


Bill, 1, 1000

Jenny, 1, 1001

Debbie, 1, 1002

When there are tie breakers...

[Agent Name],[Sale Count],[ID]
Bill, 1, 1000
Jenny, 1, 1001
Debbie, 1, 1002


I don't actually need the [ID] of course, it just needs to be able to be sorted in this manner... where [ID] represents the lowest [ID] number for that agent... (their first order of the day)

Summary: If the agents all had 1 order, the agent who got the first order wins.  If two agents have 2 orders, and everyone else has 1, the agent with the lowest [id] of the top two, wins.

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Partha Mandayam


in the sql fiddle, Bill, Jenny and Debbie all have 1 sale only
THen why bill is ranked 2 and debbie 4
What's the logic?
Ron Malmstead

Thanks _agx_,

That helped me get where I wanted to go!

Here is the final query I was going for.

;WITH cte AS
          , ROW_NUMBER() OVER(
               ORDER BY [Sale Count] DESC, [ID] ASC
          ) AS RankNum
   FROM   (
 SELECT COUNT([Agent]) as [Sale Count],[Agent],[ID] FROM  dbo.tblOrders
  WHERE CAST([Write Date] as Date) = CAST(GetDate() as Date)

GROUP BY [Agent],[ID]

   ) as tbl1

SELECT SUM([Sale Count]) as [Sale Count],[Agent],SUM(RankNum) as Ranked
FROM   cte 
GROUP BY [Agent]
ORDER BY [Sale Count] desc,[Ranked] asc

Open in new window


@Partha - The ranking is based on sales count first, then the "ID"

        The tie breaker is the agent who made their sale first, which is the lower [id] in the table. 
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

@Ron Malmstead - I think a simple COUNT(*) would do the same thing.  

Also if the date column is indexed, best to keep the comparison SARGable. Another way to write the comparison is   "WHERE Col >= today AND Col < tomorrow" or:
WITH cte AS 
   SELECT  *
        -- Rank all orders by date first, then ID as the tie breaker
        , ROW_NUMBER() OVER(
               ORDER BY [WriteDate], [ID] ASC
        ) AS OrderRankNum
   FROM   tblOrders
   WHERE  WriteDate >= CAST(GetDate() AS DATE)
   AND    WriteDate < CAST( DATEADD(d, 1, GetDate()) AS DATE)
SELECT Agent, COUNT(*) AS [Sale Count] 
FROM   cte 
-- In the event of a tie, whoever has the 1st earliest order wins

Open in new window