## Forum Stats

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

Discussions

# Null value for repeated records

Member Posts: 282 Blue Ribbon
edited Apr 29, 2015 9:07AM

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:

## Best Answer

• 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

• 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.

• 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>
```
This discussion has been closed.