CODEWITHSUNDEEP
sqloracle-databaseranking-functions
Santosh
Santosh

Reputation: 2505

display null value using rank functions in oracle sql

This is an extension of another question: How to display null value when the record is present more than one time in oracle sql

I have a table set like following:

c_id    c_name     c_tax

1001    Element1   1 
1001    Element1   2
1001    Element2   1
1001    Element2   2
1002    Element3   null
1002    Element4   1
1002    Element4   2

I want to display null in the first column(c_id) if it is present more than once and yes or no in the 3rd column(c_tax) based on the following condition.

Element1 has two taxes 1 and 2. So Element1 should be displayed once and c_tax should be yes. Element3 has no tax so it should be displayed as null.

My output should look like following:

c_id    c_name     c_tax

1001    Element1   Yes 
null    Element2   Yes
1002    Element3   No
null    Element4   Yes

Upvotes: 1

Views: 174

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

If I understand correctly:

select (case when row_number() over (partition by cid order by c_name) = 1 then cid end) as cid,
       c_name,
       (case when max(c_tax) is not null then 'yes' else 'no' end) as c_tax
from t
group by c_id, c_name
order by c_id, c_name;

Upvotes: 1

Related Questions