4

I'm using the following statement in a query:

 CASE WHEN apercent IS NULL THEN NULL ELSE dense_rank() over (partition by adate order by apercent desc) END as arank

For some reason the ranked results are starting at 2 rather than 1. There are no tied values, but there are some NULL values. I'm assuming it's because of the NULLs. Any way to update this to force the rank to begin at 1?

Current Results:

arank apercent
2 23.57
3 13.61
4 10.67
5 6.14
6 6.00
7 5.79
8 5.57
9 5.27
10 4.80
NULL NULL

Desired Result:

arank apercent
1 23.57
2 13.61
3 10.67
4 6.14
5 6.00
6 5.79
7 5.57
8 5.27
9 4.80
NULL NULL
tennis_fan89
  • 115
  • 7

2 Answers2

4

A simple solution is to use NULLS LAST:

(CASE WHEN apercent IS NOT NULL
      THEN dense_rank() over (partition by adate order by apercent desc nulls last)
 END) as arank

Alternatively, you could partition by NULL values so they are not included in the counting for non-NULL values:

(CASE WHEN apercent IS NOT NULL
      THEN dense_rank() over (partition by adate, apercent is null order by apercent desc)
 END) as arank
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

If your percentages can't be negative, you could mimic nulls last by using coalesce

case when apercent is null then null 
     else dense_rank() over (partition by adate order by coalesce(apercent,0.00) desc) 
end as arank
Radagast
  • 5,102
  • 3
  • 12
  • 27