SQL Oracle Query - Select Statement

Question

I have a table and in this table there are sometimes single or duplicate lines with 1 different attribute. This attribute is typically either null or with a value. How can I write a select statement to return all without duplicates and pick the data that has the value and where it doesnt, then return the attribute for null.

e.g.

ID   SD     FEED
0016 21AE   GF-HF   
0016 21AE   null
0017 21BE   FF-HF   
0017 21BE   null
0018 21CE   CF-HF   
0018 21CE   null
0019 21DE   null    
0019 21DE   null

Should return from the select statement: (no duplicates)

ID   SD     FEED
0016 21AE   GF-HF   
0017 21BE   FF-HF   
0018 21CE   CF-HF   
0019 21DE   null

which value should be picked up when the attribute is not null? — Vamsi Prabhala
– Vamsi Prabhala, Commented Apr 28, 2016 at 13:20
Have you tried something already? What about if doesn't work? — Lexi
– Lexi, Commented Apr 28, 2016 at 13:21
@MaheswaranRavisankar, this doesnt pull everything. If you look at the question above, I am trying to pull all including null's where there is no value. — No Body
– No Body, Commented Apr 28, 2016 at 13:56
select id, sd, max(nvl(feed, ' ')) feed from yourtable group by id, sd. — T Gray
– T Gray, Commented Apr 28, 2016 at 17:55

ScaisEdge · Accepted Answer · 2016-04-28 13:32:42Z

2

You can use an aggregate function max and group by

select id, sd, max(feed) 
from my_table
group by id, sd;

answered Apr 28, 2016 at 13:32

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

No Body Over a year ago

This would only return the single top max result and it doesnt include the null's or am I missing something?

ScaisEdge Over a year ago

should return also the null .. eventually you can use nvl()

Ponder Stibbons · Accepted Answer · 2016-04-28 14:56:08Z

1

select distinct id, sd, feed
  from (select id, sd, feed, max(feed) over(partition by id, sd) as mf from t)
 where (feed is not null and mf is not null)
    or (feed is null and mf is null)
 order by id, sd, feed

I understood, that you want all distinct values of FEED for each combination of (ID, SD) and nulls only when no other value exists. Instead of max() you can use min() in analytic version.

Test:

create table t ( ID varchar2(10), SD varchar2(10), FEED varchar2(10));
insert into t values (0016, '21AE', 'GF-HF' );
insert into t values (0016, '21AE', 'GF-HF' );
insert into t values (0016, '21AE', 'AF-AF' );
insert into t values (0016, '21AE', null );
insert into t values (0017, '21BE', 'FF-HF' );
insert into t values (0017, '21BE', null );
insert into t values (0018, '21CE', 'CF-HF' );
insert into t values (0018, '21CE', null );
insert into t values (0019, '21DE', null );
insert into t values (0019, '21DE', null );

ID         SD         FEED
---------- ---------- ----------
16         21AE       AF-AF
16         21AE       GF-HF
17         21BE       FF-HF
18         21CE       CF-HF
19         21DE

answered Apr 28, 2016 at 14:56

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

1 Comment

No Body Over a year ago

It took me a bit to figure it out but this was actually very helpful. Thanks

Collectives™ on Stack Overflow

SQL Oracle Query - Select Statement

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related