Forum Stats

  • 3,770,503 Users
  • 2,253,127 Discussions
  • 7,875,489 Comments

Discussions

Null value for repeated records

User_7PZDE
User_7PZDE Member Posts: 282 Blue Ribbon
edited Apr 29, 2015 9:07AM in SQL & PL/SQL

Hi Team,

As per the below query Deptno column is repeated records should show as null i.e

my output should be

    DEPTNO ENAME             SAL         RN        RNK       DRNK
---------- ---------- ---------- ---------- ---------- ----------
        10 KING             5000          1          1          1 
            CLARK            2450          2          2          2 
            MILLER           1300          3          3          3 
        20 SCOTT            3000          1          1          1 
            FORD             3000          2          1          1 
           JONES            2975          3          3          2 
        30 BLAKE            2850          1          1          1 
            ALLEN            1600          2          2          2 
            TURNER           1500          3          3          3 
           MARTIN           1250          4          4          4 
           WARD             1250          5          4          4 
           JAMES             950          6          6          5 

14 rows selected

select deptno
  , ename,sal,
     row_number()
       over (partition by deptno
             order by sal desc)rn,
     rank()
       over (partition by deptno
             order by sal desc)rnk,
     dense_rank()
       over (partition by deptno
            order by sal desc)drnk
    from emp
   order by deptno, sal desc
   /
   DEPTNO ENAME             SAL         RN        RNK       DRNK
---------- ---------- ---------- ---------- ---------- ----------
        10 KING             5000          1          1          1 
        10 CLARK            2450          2          2          2 
        10 MILLER           1300          3          3          3 
        20 SCOTT            3000          1          1          1 
        20 FORD             3000          2          1          1 
        20 JONES            2975          3          3          2 
        20 ADAMS            1100          4          4          3 
        20 SMITH             800          5          5          4 
        30 BLAKE            2850          1          1          1 
        30 ALLEN            1600          2          2          2 
        30 TURNER           1500          3          3          3 
        30 MARTIN           1250          4          4          4 
        30 WARD             1250          5          4          4 
        30 JAMES             950          6          6          5 


 14 rows selected 
Tagged:
Frank Kulash

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Apr 29, 2015 9:07AM Accepted Answer

    Hi,

    If you need to do this in SQL, then use a CASE expression to return deptno for the first row of each deptno, and NULL for all the others.  Use ROW_NUMBER to determine which is the first row, like this:

    select

        CASE

            WHEN  ROW_NUMBER () OVER ( PARTITION BY  deptno

                                       ORDER BY      sal  DESC

                                     )  = 1

            THEN  deptno

            ELSE  NULL    -- Default; can be omitted

        END   AS deptno

      , ename,sal, 

         row_number() 

           over (partition by deptno 

                 order by sal desc)rn, 

         rank() 

           over (partition by deptno 

                 order by sal desc)rnk, 

         dense_rank() 

           over (partition by deptno 

                order by sal desc)drnk 

        from scott.emp 

       order by emp.deptno, sal desc 

       /


    Notice how I used emp.deptno in the query ORDER BY clause.  Just plain deptno in that place would mean the results of the CASE expression, not the original column.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Apr 29, 2015 9:07AM Accepted Answer

    Hi,

    If you need to do this in SQL, then use a CASE expression to return deptno for the first row of each deptno, and NULL for all the others.  Use ROW_NUMBER to determine which is the first row, like this:

    select

        CASE

            WHEN  ROW_NUMBER () OVER ( PARTITION BY  deptno

                                       ORDER BY      sal  DESC

                                     )  = 1

            THEN  deptno

            ELSE  NULL    -- Default; can be omitted

        END   AS deptno

      , ename,sal, 

         row_number() 

           over (partition by deptno 

                 order by sal desc)rn, 

         rank() 

           over (partition by deptno 

                 order by sal desc)rnk, 

         dense_rank() 

           over (partition by deptno 

                order by sal desc)drnk 

        from scott.emp 

       order by emp.deptno, sal desc 

       /


    Notice how I used emp.deptno in the query ORDER BY clause.  Just plain deptno in that place would mean the results of the CASE expression, not the original column.

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Apr 29, 2015 8:58AM

    Where are you displaying the output? For example in SQL Plus you can do this

    SQL> break on deptno
    
    SQL> select deptno, ename from emp order by deptno;     DEPTNO ENAME
    ---------- ------
            10 CLARK
               KING
            20 SCOTT
               ADAMS
               SMITH
               JONES
            30 TURNER
               BLAKE
               MARTIN
               ALLEN
               WARD
            50 RAM
               RAM
               SHYAM 14 rows selected. SQL>
    Frank Kulash
This discussion has been closed.