Oracle - Query based up on multiple values

Question

I have data existing in a Oracle with 2 columns.

Name                ID
Default             123
Arod                234
Default             589
Green               589

I would need to write a query based on -

1) When multiple records are existing for each ID, I would need to extract the record that's not default. In this case I need to extract the record with Name "Green" for ID 589. Max 2 records will exists for each ID and one will be default

2) When single record exists for each ID, I just need to populate the name associated with that ID.

Any pointers will be greatly appreciated.

Gordon Linoff · Accepted Answer · 2018-01-23 20:10:37Z

1

You can do this with union all and some other logic:

select t.*
from t
where t.name = 'Default' and
      not exists (select 1 from t t2 where t2.id = t.id and t2.name <> t.name)
union all
select t.*
from t
where t.name <> 'Default' ;

The logic here is that you always take a non-Default name. You take the Default name when no other name exists.

Another way to approach this sort of query is with row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by case when name = 'Default' then 2 else 1 end) as seqnum
      from t
     ) t
where seqnum = 1;

answered Jan 23, 2018 at 20:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Littlefoot · Accepted Answer · 2018-01-23 21:05:35Z

This is more an "anti-example" you probably shouldn't use as Gordon already provided a smart solution.

Though, I'm kind of bored and have some time to spare, trying to find yet another way to select values you need. So - here you are.

The CASE says: if there's only one name and it is "Default", then take it. Otherwise, convert "Default" to an empty string and pick the remaining name (which will then be MAX for that ID).

SQL> with test (name, id) as
  2  (select 'Default', 123 from dual union
  3   select 'Arod'   , 234 from dual union
  4   select 'Default', 589 from dual union
  5   select 'Green'  , 589 from dual union
  6   select 'Default', 700 from dual union
  7   select 'Beatrix', 700 from dual
  8  )
  9  select id,
 10    case when count(distinct name) = 1 and
 11              max(name) = 'Default'
 12              then 'Default'
 13         else
 14           max(replace(name, 'Default', ''))
 15         end name
 16  from test
 17  group by id
 18  order by id;

        ID NAME
---------- -------
       123 Default
       234 Arod
       589 Green
       700 Beatrix

SQL>

Marmite Bomber · Accepted Answer · 2018-01-24 09:10:47Z

You may want to hide the complexity of the data access behind a view.

The view hat primary key on ID and return the value and default value in two column. Additionally the view checks the consistence of the data - in your case only two records per ID are allowed (see column status)

create view my_view as 
select id,
   max(case when name != 'Default' then  name end) name,
   max(case when name = 'Default' then  name end) default_name, 
   case when count(*) > 2 then 'Invalid ID, count '||count(*) end as status
from tab
group by id;

select * from my_view order by id;

        ID NAME    DEFAULT_NAME STATUS                                                   
---------- ------- ------------ ----------------------------------------------------------
       123         Default                                                                 
       234 Arod                                                                            
       589 Green   Default                                                                 
       700 Beatrix Default

Querying the data is as easy as to use the NVL on NAME and DEFAULT_NAME

select id, nvl(NAME,  DEFAULT_NAME) name from my_view order by id;  

        ID NAME  
---------- -------
       123 Default 
       234 Arod    
       589 Green   
       700 Beatrix

Collectives™ on Stack Overflow

Oracle - Query based up on multiple values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related