CODEWITHSUNDEEP
sqloracle-database
RKumar
RKumar

Reputation: 11

Distinct rows with non-null values taking precedence over nulls

I have two duplicated two rows except one column of the row has null value and another one has some value. For example

CarName  Owner  PreviousOwner
Honda    XXX    YYYYY
Honda    XXX    
Nissan   ZZZ    AAAA
Nissan   ZZZ    
BMW      BBB
Benz     CCC    DDD

OUTPUT should be

Honda    XXX    YYYYY
Nissan   ZZZ    AAAA
BMW      BBB
Benz     CCC    DDD

Can you please help me to write a query for this.

Upvotes: 1

Views: 81

Answers (3)

ForeverLearning
ForeverLearning

Reputation: 13

If we were to order the records such that the previous owner with a value came on top, then all you would have to do is select the topmost row. In order to achieve that, I used the row_number() along with ordering of previous_owner. Please see below:

select carname, owner, previous_owner from 
(select cars.*, rowid, row_number() over (partition by carname, owner order by 
previous_owner) rid 
from cars) 
where rid <=1;

Upvotes: 1

Gauravsa
Gauravsa

Reputation: 6514

One way is:

select t.CarName, t.Owner, r.PreviousOwner
from (select Carname, max(previousowner)
       from test 
       group by carname) r
inner join test t 
on t.carname = r.carname and t.previousowner = r.previousowner
group by t.CarName, t.Owner, r.PreviousOwner;

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

Check this

select CarName, Owner, max(PreviousOwner)
from t
group by CarName, Owner

Upvotes: 0

Related Questions