Skip to Main Content
  • Questions
  • Combining multiple rows with a "priority"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Scott.

Asked: May 03, 2017 - 2:07 pm UTC

Last updated: May 04, 2017 - 3:39 pm UTC

Version: 11G

Viewed 10K+ times! This question is

You Asked

I have a table:

create table mytab (ROLE_NAME VARCHAR2(30), MENU_TAB VARCHAR2(20), colA VARCHAR2(1), colB VARCHAR2(1), colC VARCHAR2(1));


I populate:

INSERT INTO MYTAB VALUES('TECH','MAIN','A','B', NULL);
INSERT INTO MYTAB VALUES('TECH','SUB','B','C', NULL);
INSERT INTO MYTAB VALUES('TEMP','MAIN','B','A','C');
INSERT INTO MYTAB VALUES('OPER','MAIN',NULL, 'C','B');

SQL> select * from mytab;

ROLE_NAME                      MENU_TAB             colA colB  colC
------------------------------ -------------------- ---- ----  ----
TECH                           MAIN                 A     B    NULL
TECH                           SUB                  B     C    NULL
TEMP                           MAIN                 B     A     C
OPER                           MAIN                 NULL  C     B


In my data, ROLE_NAME||MENU_TAB will always be unique.

I'm looking for a way to aggregate the results of the following query into a single row.

SQL> select colA, colB, colC
     from mytab
     where menu_tab = 'MAIN' and role_name in ('TECH','OPER');


The query returns 2 rows (of course) - With the data I'm working with, it will always return 1 or 2 rows.

colA colB  colC
---- ----  ----
A     B    NULL       (<i> From the <TECH> record</i>)
NULL  C     B         (<i> From the <OPER> record</i>)


I want to return a single aggregated row of all the data found in all records with priority given to a chosen ROLE_NAME in cases where there is a value in the same column for two rows.

I'm trying to get this:

colA colB  colC
---- ----  ----
A     C      B


In this case, colA and colC are from the 'TECH' record, colB is from the 'OPER' record because I want to designate the 'OPER' data as "priority" in cases where multiple rows have a value in the same column.

Is this possible in a single SQL query? (It's for an Oracle Forms project & needs to become a WHERE clause for a block...) Initially I thought this should be simple but I'm beginning to believe that it must be me that's "simple". Any assistance gratefully accepted.



and Chris said...

It's possible in one SQL statement. I wouldn't say it's obvious though, so don't be too hard on yourself ;)

The first thing you'll want to do is assign rows a number according to their priority. For example:

row_number() over (order by role_name)


Adjust the order by clause as needed. You can then find the values from the lowest (top) priority row with first_value using the ignore nulls clause

with rws as (
  select colA, colB, colC, row_number() over (order by role_name) rn
  from   mytab
  where  menu_tab = 'MAIN' and role_name in ('TECH','OPER')
), vals as (
  select first_value(colA) ignore nulls over (order by rn) cola,
         first_value(colB) ignore nulls over (order by rn) colb,
         first_value(colC) ignore nulls over (order by rn) colc
  from   rws
)
  select * from vals;

COLA  COLB  COLC  
      C     B     
A     C     B


At this point you still have two rows and some nulls. Here are two methods you can use to squash them out and get one row:

Group By & Min

Min (or max) ignore the nulls. So you can group by any other columns you need and apply this to the result of the first_value:

with rws as (
  select colA, colB, colC, row_number() over (order by role_name) rn
  from   mytab
  where  menu_tab = 'MAIN' and role_name in ('TECH','OPER')
), vals as (
  select first_value(colA) ignore nulls over (order by rn) cola,
         first_value(colB) ignore nulls over (order by rn) colb,
         first_value(colC) ignore nulls over (order by rn) colc
  from   rws
)
  select min(cola), min(colb), min(colc) from vals;

MIN(COLA)  MIN(COLB)  MIN(COLC)  
A          C          B  


First_value Windowing Clause

The default windowing clause stops at the current row. Use "rows between unbounded preceding and unbounded following" to consider all rows in the data set and avoid unwanted nulls:

with rws as (
  select colA, colB, colC, row_number() over (order by role_name) rn
  from   mytab
  where  menu_tab = 'MAIN' and role_name in ('TECH','OPER')
), vals as (
  select first_value(colA) ignore nulls over (order by rn 
           rows between unbounded preceding and unbounded following) cola,
         first_value(colB) ignore nulls over (order by rn
           rows between unbounded preceding and unbounded following) colb,
         first_value(colC) ignore nulls over (order by rn
           rows between unbounded preceding and unbounded following) colc
  from   rws
)
  select * from vals;

COLA  COLB  COLC  
A     C     B     
A     C     B     


You then need to group by/distinct the results so you only get one row.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Wow. It works!

Scott Morrow, May 03, 2017 - 4:08 pm UTC

Wow Chris... That worked perfectly, even after expanding the select to cover all 28 fields I need (A-Z). Have you got that procedure memorized or something? I always get great answers here but I didn't expect a working answer SO quickly! You're amazing!

I'll have to study your answer for a bit to understand how it works but I don't feel too bad about not figuring it out on my own now - I would never have come up with as elegant a solution as you. THANK YOU!!

One more question

Scott Morrow, May 03, 2017 - 5:50 pm UTC

Chris,

I'm studying your query and am not sure I understand one part - The "over (ORDER_BY ROLE_NAME)" - Would that not set the "priority" returned value to be from the row sorted by the value in ROLE_NAME alphabetically rather than the priority of one or the other that I choose?

e.g. if the two ROLE_NAMES happened to be "ZENITH" and "ABERCROMBIE", if I sort by role_name to determine which of the two should take priority, would it not always choose the value associated with whichever is greater alphabetically (Zenith > Abercrombie)?

There will be dozens of different ROLE_NAMES records in the table. The query will always select from TWO of the existing role_names (...and ROLE_NAME in 'WHATEVER1','WHATEVER2') but I'm not sure how the query is specifying which of the two role_name records will be considered "priority" when a value exists in the same column for both role_names.

Any clarification possible?

Chris Saxon
May 04, 2017 - 8:36 am UTC

You're right: "order by role_name" does sort alphabetically. This just happened to be the correct order for this scenario. You need to change this to suit your priority.

If you want this to be dynamic you need to know which is the priority role. You can then map this to 1 in the order by and everything else to 2.

For example:

var pri_role varchar2(10);

exec :pri_role := 'TECH';

select role_name, 
       row_number() over (order by case when role_name = :pri_role then 1 else 2 end) rn 
from   mytab;

ROLE_NAME  RN  
TECH       1   
TECH       2   
TEMP       3   
OPER       4  

exec :pri_role := 'OPER';

select role_name, 
       row_number() over (order by case when role_name = :pri_role then 1 else 2 end) rn 
from   mytab;

ROLE_NAME  RN  
OPER       1   
TEMP       2   
TECH       3   
TECH       4   

Very useful!!

Venkat Sagaram, May 03, 2017 - 7:14 pm UTC

Super!! This is something that I was looking for, to use in one of my applications!! Always get great answers here!!

Let me know when you are in this part of the world Chris!!

Thank You!!
Connor McDonald
May 04, 2017 - 1:45 am UTC

glad we could help, thanks for the feedback

aggregation

Rajeshwaran, May 04, 2017 - 10:27 am UTC

Sorry for being late in this.

so give this

....
In this case, colA and colC are from the 'TECH' record, colB is from the 'OPER' record because I want to designate the 'OPER' data as "priority" in cases where multiple rows have a value in the same column.
.....


wont it be sufficient to have simple aggregation than analytics here? something like this.

demo@ORA12C> variable x varchar2(10)
demo@ORA12C> exec :x :='OPER';

PL/SQL procedure successfully completed.

demo@ORA12C> select * from mytab;

ROLE_NAME                      MENU_TAB             C C C
------------------------------ -------------------- - - -
TECH                           MAIN                 A B
TECH                           SUB                  B C
TEMP                           MAIN                 B A C
OPER                           MAIN                   C B

demo@ORA12C> select max(cola) keep(dense_rank first order by decode(:x,'OPER',1,2) ) cola,
  2         max(colb) keep(dense_rank first order by decode(:x,'OPER',1,2) ) colb,
  3         max(colc) keep(dense_rank first order by decode(:x,'OPER',1,2) ) colc
  4  from mytab
  5  where menu_tab = 'MAIN' and
  6  role_name in ('TECH','OPER');

C C C
- - -
A C B

demo@ORA12C>

Chris Saxon
May 04, 2017 - 3:39 pm UTC

That gives you the same result whatever you pass for the bind:

VAR x varchar2(10);

exec :x := 'TECH';
select max(cola) keep(dense_rank first order by decode(:x,'OPER',1,2) ) cola,
       max(colb) keep(dense_rank first order by decode(:x,'OPER',1,2) ) colb,
       max(colc) keep(dense_rank first order by decode(:x,'OPER',1,2) ) colc
from mytab
where menu_tab = 'MAIN' and
role_name in ('TECH','OPER');

COLA  COLB  COLC  
A     C     B 

exec :x := 'OPER';

select max(cola) keep(dense_rank first order by decode(:x,'OPER',1,2) ) cola,
       max(colb) keep(dense_rank first order by decode(:x,'OPER',1,2) ) colb,
       max(colc) keep(dense_rank first order by decode(:x,'OPER',1,2) ) colc
from mytab
where menu_tab = 'MAIN' and role_name in ('TECH','OPER');

COLA  COLB  COLC  
A     C     B     


Even if you use the case expression to define the ordering it still doesn't work because it doesn't ignore the nulls:

VAR x varchar2(10);

exec :x := 'TECH';
select max(cola) keep(dense_rank first order by case when role_name = :x then 1 else 2 end ) cola,
       max(colb) keep(dense_rank first order by case when role_name = :x then 1 else 2 end ) colb,
       max(colc) keep(dense_rank first order by case when role_name = :x then 1 else 2 end ) colc
from mytab
where menu_tab = 'MAIN' and
role_name in ('TECH','OPER');

COLA  COLB  COLC  
A     B   

exec :x := 'OPER';

select max(cola) keep(dense_rank first order by case when role_name = :x then 1 else 2 end ) cola,
       max(colb) keep(dense_rank first order by case when role_name = :x then 1 else 2 end ) colb,
       max(colc) keep(dense_rank first order by case when role_name = :x then 1 else 2 end ) colc
from mytab
where menu_tab = 'MAIN' and role_name in ('TECH','OPER');

COLA  COLB  COLC  
      C     B  

aggregation

Rajeshwaran, May 05, 2017 - 4:39 am UTC

Thanks Chirs. missed out the NULL part completely.

as you said, extending the "Window" clause of the analytics, provides the required results.

demo@ORA12C> variable x varchar2(10)
demo@ORA12C> exec :x := 'OPER';

PL/SQL procedure successfully completed.

demo@ORA12C> select t.*,
  2        first_value( cola ignore nulls) over( order by decode(role_name,:x,1,2)
  3            rows between unbounded preceding and unbounded following ) new_cola,
  4        first_value( colb ignore nulls) over( order by decode(role_name,:x,1,2)
  5            rows between unbounded preceding and unbounded following ) new_colb,
  6        first_value( colc ignore nulls) over( order by decode(role_name,:x,1,2)
  7            rows between unbounded preceding and unbounded following ) new_colc
  8  from mytab t
  9  where  menu_tab = 'MAIN'
 10  and role_name in ('TECH','OPER');

ROLE_NAME                      MENU_TAB             C C C N N N
------------------------------ -------------------- - - - - - -
OPER                           MAIN                   C B A C B
TECH                           MAIN                 A B   A C B

demo@ORA12C> exec :x :='TECH';

PL/SQL procedure successfully completed.

demo@ORA12C> /

ROLE_NAME                      MENU_TAB             C C C N N N
------------------------------ -------------------- - - - - - -
TECH                           MAIN                 A B   A B B
OPER                           MAIN                   C B A B B

demo@ORA12C>

then finally aggregating them provides the required results.
demo@ORA12C> exec :x :='OPER';

PL/SQL procedure successfully completed.

demo@ORA12C> select max( new_cola ), max(new_colb) ,  max(new_colc)
  2  from (
  3  select t.*,
  4        first_value( cola ignore nulls) over( order by decode(role_name,:x,1,2)
  5            rows between unbounded preceding and unbounded following ) new_cola,
  6        first_value( colb ignore nulls) over( order by decode(role_name,:x,1,2)
  7            rows between unbounded preceding and unbounded following ) new_colb,
  8        first_value( colc ignore nulls) over( order by decode(role_name,:x,1,2)
  9            rows between unbounded preceding and unbounded following ) new_colc
 10  from mytab t
 11  where  menu_tab = 'MAIN'
 12  and role_name in ('TECH','OPER')
 13      ) ;

M M M
- - -
A C B

demo@ORA12C> exec :x :='TECH';

PL/SQL procedure successfully completed.

demo@ORA12C> /

M M M
- - -
A B B

demo@ORA12C>

Regarding Rajeshwaran's suggestion for a simple aggregation solution

Oren Nakdimon, May 09, 2017 - 1:58 pm UTC

select max(cola) keep(dense_rank first order by nvl2(cola,decode(role_name,:x,1,2),3)) cola,
       max(colb) keep(dense_rank first order by nvl2(colb,decode(role_name,:x,1,2),3)) colb,
       max(colc) keep(dense_rank first order by nvl2(colc,decode(role_name,:x,1,2),3)) colc
from mytab
where menu_tab = 'MAIN' and role_name in ('TECH','OPER');

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.