CODEWITHSUNDEEP
sqloracle-databaseoracle11g
MKN
MKN

Reputation: 497

how to assign a rank for non null values in oracle

I need to assign a rank in such a way that it ignores null value.

select root_cause_desc,
       case 
         when root_cause_desc is null 
         then null  
         else rank() over ( order by excess_value desc) 
       end gap_rank 
 from table.
where root_cause_desc is not null

gives

ROOT_CAUSE_DESC EXCESS_VALUE    TOTAL_EXCESS_VALUE_WK   GAP_RANK

advanced shipment   120.9750138     -760356.4054             10
dfdfdfdf222                0        -1696000.946             11
Root Cause         -0.0760554       -760356.4054             12
test one more      -656.277192      -760356.4054             13
earlier truck      -77099.35         720093.3712             14

It ignores the null value and assign it the rank even for null root cause. I want gap_rank as 1,2,3,4. Please let me now how to do this.

Upvotes: 3

Views: 4650

Answers (2)

Ben
Ben

Reputation: 52893

The problem is that RANK() is independent of your case statement; it's ordering the entire query by the ORDER BY clause you give it.

Utilise the NULLS LAST keywords to put the NULL values at then end of the order and then your CASE statement will work. For instance: 

with the_data as (
 select level as a
      , nullif(nullif(level, 5), 8) as b
   from dual
 connect by level <= 10
         )
 select a
      , b
      , case when b is null then null
             else rank() over ( order by case when b is not null then 1
                                         end nulls last
                                        , a )
        end as "rank"
   from the_data
  order by a;

         A          B       rank
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5
         6          6          5
         7          7          6
         8
         9          9          7
        10         10          8

10 rows selected.

SQL Fiddle

Upvotes: 3

Gaurav Soni
Gaurav Soni

Reputation: 6336

I think there is not need to put a check of root_cause_desc is null in the select clause. The where ,group by order by clause executes first ,then the analytical function is processed.So ,before processing your rank , it will eliminates the null root_cause_desc.

WITH tab
     AS (SELECT NULL root_cause, 5 AS val FROM DUAL
         UNION ALL
         SELECT 'A' root_cause, 1 AS val FROM DUAL
         UNION ALL
        SELECT NULL root_cause, 4 AS val FROM DUAL
         UNION ALL
         SELECT 'A' root_cause, 2 AS val FROM DUAL
         UNION ALL
         SELECT NULL root_cause, 3 AS val FROM DUAL)
SELECT root_cause, val, RANK () OVER (ORDER   BY val DESC) rnk
  FROM tab
 WHERE root_cause IS NOT NULL;

root_casue   val     rnk
=========================

A           2          1
A           1          2
===========================

Upvotes: 0

Related Questions