CODEWITHSUNDEEP
sqloracle-database
Vic VKh
Vic VKh

Reputation: 311

Join rows with minimum and maximum value on a specific column

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. E.g.:

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      1 Q     <-- C1: THIS MIN(NUM)
C1   B2      2 W
C1   B3      3 E
C1   B4      4 R     <-- C1: THIS MAX(NUM)

7 rows selected.

The expected result set is

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

Please advise how to achieve the desired result.

Upvotes: 0

Views: 191

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272066

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

Upvotes: 1

MT0
MT0

Reputation: 167867

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

db<>fiddle here

Upvotes: 3

Related Questions