TSQL2005 - DENSE_RANK and Handling NULLs

• hamilton_taylor

  SSC Veteran

  Points: 225

  More actions

  June 24, 2008 at 9:53 pm

  #208749

  Hello All. I would be greatful for some assistance. I am trying to RANK a calculated metric and when I have NULL fields it ranks the NULLs at the top. I have been unable to find the syntax control such.

  The only way I have found around this issue is hard setting the NULL value with a CASE WHEN, but this presents some issues with future calculations.

  Here is an example:

  CASE WHEN SUM(h.totalscore) IS NULL THEN 18

  ELSE DENSE_RANK() OVER (PARTITION BY a.bus_manager_name, c.NumericFiscalWeek ORDER BY MAX(h.totalscore) DESC) END AS 'QualityRank'

  I am Partitioning by the bus_manager_name and NumbericFiscalWeek to break it down by teams and to metric performance over time. The metric i am ranking is totalscore. I need to drop the NULLs to the bottom of the rank or maybe a suggestion regarding another avenue of tackling this problem?

  Removing the CASE WHEN I get NULLs ranked at the top with subsequent number ranked following. I have tried RANK also to no avail.

  Any ideas suggestions regarding a better way to handle this? Thanks much!

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  June 25, 2008 at 2:12 am

  #833752

  Tried this?

  SELECT DENSE_RANK() OVER (PARTITION BY a.bus_manager_name, c.NumericFiscalWeek ORDER BY ISNULL(MAX(h.totalscore), 18) DESC) END AS QualityRank

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 25, 2008 at 8:09 am

  #834001

  Using IsNull/Coalesce in the Partition By or Order By portion of Row_Number, Rank, or Dense_Rank works. I just tested it.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon