Join rows with minimum and maximum value on a specific column-CodePudding

I have a table that looks like this:

COL ID_1 FOR A4
COL ID_2 FOR A4
COL ID_3 FOR A4

SELECT 'A1' ID_1, 'B1' ID_2, 10 NUM, 'X' ID_3 FROM DUAL
UNION ALL
SELECT 'A1' ID_1, 'B2' ID_2, 20 NUM, 'Y' ID_3  FROM DUAL
UNION ALL
SELECT 'A1' ID_1, 'B3' ID_2, 30 NUM, 'Z' ID_3  FROM DUAL
UNION ALL
SELECT 'C1' ID_1, 'B1' ID_2, 1  NUM, 'Q' ID_3 FROM DUAL
UNION ALL
SELECT 'C1' ID_1, 'B2' ID_2, 2  NUM, 'W' ID_3  FROM DUAL
UNION ALL
SELECT 'C1' ID_1, 'B3' ID_2, 3  NUM, 'E' ID_3  FROM DUAL
UNION ALL
SELECT 'C1' ID_1, 'B4' ID_2, 4  NUM, 'R' ID_3  FROM DUAL;


ID_1 ID_2        NUM ID_3
---- ---- ---------- ----
A1   B1           10 X
A1   B2           20 Y
A1   B3           30 Z
C1   B1            1 Q
C1   B2            2 W
C1   B3            3 E
C1   B4            4 R

7 rows selected.

I want to join rows with minimum and maximum values on the NUM column. Result set grouped by ID_1 column

ID_1 ID_2        NUM ID_3
---- ---- ---------- ----
A1   B1           10 X     <-- A1: THIS MIN(NUM)
A1   B2           20 Y
A1   B3           30 Z     <-- A1: THIS MAX(NUM)
C1   B1            4 Q     <-- C1: THIS MAX(NUM)
C1   B2            3 W
C1   B3            2 E
C1   B4            1 R     <-- C1: THIS MIN(NUM)

7 rows selected.

The expected result set is

A1 B1 10 X B3 30 Z 
C1 B4 1  R B1 4  Q

Please advise how to achieve the desired result.

CodePudding user response：

You can do it without any self-joins using aggregation functions with KEEP (DENSE_RANK [FIRST|LAST] ORDER BY ...):

SELECT id_1,
       MIN(id_2) KEEP (DENSE_RANK FIRST ORDER BY num) AS min_id_2,
       MIN(num) AS min_num,
       MIN(id_3) KEEP (DENSE_RANK FIRST ORDER BY num, id_2) AS min_id_3,
       MAX(id_2) KEEP (DENSE_RANK LAST ORDER BY num) AS max_id_2,
       MAX(num) AS max_num,
       MAX(id_3) KEEP (DENSE_RANK LAST ORDER BY num, id_2) AS max_id_3
FROM   table_name
GROUP BY id_1;

Which, for the sample data, outputs:

ID_1 MIN_ID_2 MIN_NUM MIN_ID_3 MAX_ID_2 MAX_NUM MAX_ID_3

A1 B1 10 X B3 30 Z

C1 B1 1 Q B4 4 R

ID_1	MIN_ID_2	MIN_NUM	MIN_ID_3	MAX_ID_2	MAX_NUM	MAX_ID_3
A1	B1	10	X	B3	30	Z
C1	B1	1	Q	B4	4	R

db<>fiddle here

CodePudding user response：

Something like this:

with cte as (
    select
        id_1, id_2, num, id_3,
        row_number() over (partition by id_1 order by num) as rn1,
        row_number() over (partition by id_1 order by num desc) as rn2
    from t
)
select
    cte1.id_1,
    cte1.id_2, cte1.num, cte1.id_3,
    cte2.id_2, cte2.num, cte2.id_3
from cte cte1
join cte cte2 on cte1.id_1 = cte2.id_1 and cte2.rn2 = 1
where cte1.rn1 = 1