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]

Jim,2,1003

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

Jim,1,1003

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
_agx_

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
_agx_

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
or
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

_agx_

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

ASKER
Thanks _agx_,

That helped me get where I wanted to go!

Here is the final query I was going for.

;WITH cte AS
(
   SELECT *
          , 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


_agx_

@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.
rwheeler23
_agx_

@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 
GROUP BY Agent
-- In the event of a tie, whoever has the 1st earliest order wins
ORDER BY COUNT(*) DESC, MIN(OrderRankNum) ASC 

Open in new window

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9235db483bd3c4dead115f5b8962d954