How to get 2nd max value if 1st max is null

• Krish319

  Old Hand

  Points: 352

  More actions

  October 9, 2023 at 2:47 pm

  Go to Answer

  #4304510

  Hello All,

  I have written below query to get agent data who completed max tickets for each group.

  But some times the agent email address is null or blank.

  So In that case I need to consider the 2nd max tickets completed agentname.

  If this email also null then 3rd max tickets completed agent.

  Could any one please let me know how to modify below query to get my desired result.

  WITH cteRowNum(GroupName,AgentName,AgentEmail,

  TicketsCompleted, RowNums) AS (

  SELECT GroupName,AgentName,AgentEmail,TicketsCompleted,

  DENSE_RANK() OVER(PARTITION BY GroupName ORDER BY TicketsCompleted DESC) AS RowNums

  FROM Table

  )

  SELECT cteRowNum.GroupName,cterowNum.AgentName,cteRowNum.AgentEmail, cteRowNum.TicketsCompleted

  FROM cteRowNum

  WHERE cteRowNum.RowNums = 1;

• ScottPletcher

  SSC Guru

  Points: 101096

  More actions

  October 9, 2023 at 2:55 pm

  Answer

  #4304511

   

  WITH cteRowNum(GroupName,AgentName,AgentEmail,

  TicketsCompleted, RowNums) AS (

  SELECT GroupName,AgentName,AgentEmail,TicketsCompleted,

  DENSE_RANK() OVER(PARTITION BY GroupName ORDER BY TicketsCompleted DESC) AS RowNums

  FROM Table

  WHERE AgentName IS NOT NULL

  )

  SELECT cteRowNum.GroupName,cterowNum.AgentName,cteRowNum.AgentEmail, cteRowNum.TicketsCompleted

  FROM cteRowNum

  WHERE cteRowNum.RowNums = 1;

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".