You Asked
Hello,
I have a situation where my data (for a given sys_id) has values for multiple depths (level1 attribute, level2 attribute and so on). For a given sys_id, I have to select the rows that has the maximum depth. However, as an example, if a value in level3 is null for one of the rows and has values for the other rows, then I don't need to select the row that has the NULL value.
For example:
create table depth (sys_id number,subj varchar2(20),level1 varchar2(20),level2 varchar2(20),level3 varchar2(20));
insert into depth values (11,'text1','airbag');
insert into depth values (11,'text2','airbag');
insert into depth values (11,'text3','airbag','airbag_gen');
insert into depth values (11,'text4','airbag','airbag_gen');
insert into depth values (11,'text5','airbag','airbag_gen');
insert into depth values (11,'text6','airbag','airbag_ind','airbagind_p');
insert into depth values (11,'text7','airbag','airbag_ind');
insert into depth values (11,'text8','airbag','airbag_ind','airbagind_g');
insert into depth values (11,'text9','airbag','airbag_ind','airbagind_g');
insert into depth values (11,'text10','airbag','driver_airbag');
insert into depth values (11,'text11','airbag','driver_airbag');
insert into depth values (11,'text12','airbag','driver_airbag');
insert into depth values (11,'text13','airbag','passenger_airbag');
insert into depth values (11,'text14','airbag','passenger_airbag');
insert into depth values (11,'text15','airbag','passenger_airbag');
From the above input, I need to get the following rows:
11,text3,airbag,airbag_gen
11,text4,airbag,airbag_gen
11,text5,airbag,airbag_gen
11,text6,airbag,airbag_ind,airbagind_p
11,text8,airbag,airbag_ind,airbagind_g
11,text9,airbag,airbag_ind,airbagind_g
11,text10,airbag,driver_airbag
11,text11,airbag,driver_airbag
11,text12,airbag,driver_airbag
11,text13,airbag,passenger_airbag
11,text14,airbag,passenger_airbag
11,text15,airbag,passenger_airbag
Can you please help?
Thanks,
and Connor said...
I was *about* to say "thanks for the test case" but your insert's dont work !!!! grrrrr
Anyway...
SQL> drop table depth purge;
Table dropped.
SQL> create table depth (sys_id number,subj varchar2(20),level1 varchar2(20),level2 varchar2(20),level3 varchar2(20));
Table created.
SQL>
SQL> insert into depth values (11,'text1','airbag',null,null);
1 row created.
SQL> insert into depth values (11,'text2','airbag',null,null);
1 row created.
SQL> insert into depth values (11,'text3','airbag','airbag_gen',null);
1 row created.
SQL> insert into depth values (11,'text4','airbag','airbag_gen',null);
1 row created.
SQL> insert into depth values (11,'text5','airbag','airbag_gen',null);
1 row created.
SQL> insert into depth values (11,'text6','airbag','airbag_ind','airbagind_p');
1 row created.
SQL> insert into depth values (11,'text7','airbag','airbag_ind',null);
1 row created.
SQL> insert into depth values (11,'text8','airbag','airbag_ind','airbagind_g');
1 row created.
SQL> insert into depth values (11,'text9','airbag','airbag_ind','airbagind_g');
1 row created.
SQL> insert into depth values (11,'text10','airbag','driver_airbag',null);
1 row created.
SQL> insert into depth values (11,'text11','airbag','driver_airbag',null);
1 row created.
SQL> insert into depth values (11,'text12','airbag','driver_airbag',null);
1 row created.
SQL> insert into depth values (11,'text13','airbag','passenger_airbag',null);
1 row created.
SQL> insert into depth values (11,'text14','airbag','passenger_airbag',null);
1 row created.
SQL> insert into depth values (11,'text15','airbag','passenger_airbag',null);
1 row created.
SQL> select sys_id, subj, coalesce(level3,level2,level1) highest
2 from depth;
SYS_ID SUBJ HIGHEST
---------- -------------------- --------------------
11 text1 airbag
11 text2 airbag
11 text3 airbag_gen
11 text4 airbag_gen
11 text5 airbag_gen
11 text6 airbagind_p
11 text7 airbag_ind
11 text8 airbagind_g
11 text9 airbagind_g
11 text10 driver_airbag
11 text11 driver_airbag
11 text12 driver_airbag
11 text13 passenger_airbag
11 text14 passenger_airbag
11 text15 passenger_airbag
15 rows selected.
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment