oracle - group by - no aggregation

Question

I have an oracle table , where ref_id is the flag field is the type of data and ORN is the order of data in each ref_id :

ref_id   data    ORN   flag
  1       100     0     0
  1       200     1     0
  1       300     2     0
  1       400     3     0
  1       110     0     1
  1       210     1     1
  1       150     0     2
  1       250     1     2
  1       350     2     2
  1       450     3     2
  2       500     0     0
  2       600     1     0
  2       700     2     0
  2       800     3     0
  2       120     0     1
  2       220     1     1
  2       320     1     1
  2       420     1     1
  2       170     0     2
  2       270     1     2
  2       370     2     2
  2       470     3     2

I need to group the data in a way to get last data in flag 0 and last data in flag 2 for each ref_id

so the new table will be something like this:

ref_id    data_1    data_2
  1        400       450
  2        800       470

any hint how to accomplish this without using loops?

score 1 · Answer 1 · edited Apr 03 '20 at 12:04

1

You can use the analytical function and group by as follows:

SELECT REF_ID, 
       MAX(CASE WHEN FLAG = 0 THEN DATA END) AS DATA_0,
       MAX(CASE WHEN FLAG = 2 THEN DATA END) AS DATA_2
FROM
(
  SELECT REF_ID, DATA, ORN, FLAG,
         ROW_NUMBER() OVER (PARTITION BY REF_ID, FLAG ORDER BY ORN DESC) AS RN
  FROM YOUR_TABLE 
  WHERE FLAG IN (0,2)
)
WHERE RN = 1
GROUP BY REF_ID

edited Apr 03 '20 at 12:04

Thorsten Kettner

89,309
7
49
73

answered Apr 03 '20 at 11:36

Popeye

35,427
4
10
31

score 0 · Answer 2 · answered Apr 03 '20 at 12:48

Alternatively use a two step approach, first (in the CTE) select only the values of the DATA column that corresponds to the last ORN within the REF_ID

Note that is case the ORNis not unique you may get more than one row potentially with different values.

In the next step simple aggregate on REF_ID, I'm using max function, i.e. this will get that highest value of DATA in case of ties.

In case the combination of REF_ID and ORN is unique (primary key) you may use MIN and MAX interchangeable, but it is good to know that they will provide diffremt result if dups are allowed.

with agg as (
select 
 REF_ID,FLAG, DATA, ORN,
 case when flag = 0  and ORN = max(ORN) over (partition by REF_ID, FLAG) then data end as data_0,
 case when flag = 2  and ORN = max(ORN) over (partition by REF_ID, FLAG) then data end as data_2
from tab
)
select  REF_ID, 
 max(data_0) as data_0,
 max(data_2) as data_2
from agg
group by REF_ID
order by 1;

Here the result of the CTE

    REF_ID       FLAG       DATA        ORN     DATA_0     DATA_2
---------- ---------- ---------- ---------- ---------- ----------
         1          0        100          0                      
         1          0        200          1                      
         1          0        300          2                      
         1          0        400          3        400           
         1          1        110          0                      
         1          1        210          1                      
         1          2        150          0                      
         1          2        250          1                      
         1          2        350          2                      
         1          2        450          3                   450
...

and the result of the final query

    REF_ID     DATA_0     DATA_2
---------- ---------- ----------
         1        400        450
         2        800        470

Slkrasnodar · Answer 3 · 2020-04-03T16:39:47.970

You may use the aggregate functions (FIRST/LAST) for the purpose.

https://docs.oracle.com/database/121/SQLRF/functions074.htm#SQLRF00641

https://docs.oracle.com/database/121/SQLRF/functions095.htm#SQLRF00653.

with t (ref_id,data,ORN,flag) as (
select   1,       100,     0,     0 from dual union all
select   1,       200,     1,     0 from dual union all
select   1,       300,     2,     0 from dual union all
select   1,       400,     3,     0 from dual union all
select   1,       110,     0,     1 from dual union all
select   1,       210,     1,     1 from dual union all
select   1,       150,     0,     2 from dual union all
select   1,       250,     1,     2 from dual union all
select   1,       350,     2,     2 from dual union all
select   1,       450,     3,     2 from dual union all

select   2,       500,     0,     0 from dual union all
select   2,       600,     1,     0 from dual union all
select   2,       700,     2,     0 from dual union all
select   2,       800,     3,     0 from dual union all
select   2,       120,     0,     1 from dual union all
select   2,       220,     1,     1 from dual union all
select   2,       320,     1,     1 from dual union all
select   2,       420,     1,     1 from dual union all
select   2,       170,     0,     2 from dual union all
select   2,       270,     1,     2 from dual union all
select   2,       370,     2,     2 from dual union all
select   2,       470,     3,     2 from dual 
)
select 
  ref_id
, max(decode(flag, 0, data)) keep (dense_rank last order by decode(flag, 0, 100, 50), orn ) x
, max(decode(flag, 2, data)) keep (dense_rank last order by decode(flag, 2, 100, 50), orn ) y 
-- or
, min(decode(flag, 0, data)) keep (dense_rank first order by decode(flag, 0, 50, 100), orn desc) xx
, min(decode(flag, 2, data)) keep (dense_rank first order by decode(flag, 2, 50, 100), orn desc) yy

from t
group by ref_id

    REF_ID          X          Y         XX         YY
---------- ---------- ---------- ---------- ----------
         1        400        450        400        450
         2        800        470        800        470

I had the same idea, but noticed then that this reports a wrong value belonging to another flag in case a flag (0 or 2) is completely missing from the data set. (Remove all `ref_id = 1, flag = 2` to see what I mean.) — Thorsten Kettner, Apr 03 '20 at 15:11
@ThorstenKettner, thank you. updated the statement to handle the case you pointed out. — Slkrasnodar, Apr 03 '20 at 16:41

oracle - group by - no aggregation

3 Answers3