Link to home
Start Free TrialLog in
Avatar of Ron Malmstead
Ron MalmsteadFlag 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.

ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
_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?
Avatar of 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


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