CODEWITHSUNDEEP
sqloracle-database
Sid
Sid

Reputation: 582

How to get min from 2 values using Oracle SQL

I have 2 tables TAB1 and TAB2. Both have 3 columns each SEGMENT, LEVEL and VALUE. Below is the data from both tables:

TAB1

SEGMENT       LEVEL        VALUE
----------------------------------
SEG1              1        1000
SEG1              2         900
SEG1              3         800
SEG2              1        4000
SEG2              2        3000
SEG2              3        1000
SEG3              1        5000
SEG3              2        3500
SEG3              3        2200

TAB2

SEGMENT       LEVEL        VALUE
----------------------------------
SEG1              1        2000
SEG1              2        1800
SEG1              3        1200
SEG2              1        3000
SEG2              3        2500
SEG4              1       10000
SEG4              2        8100
SEG4              3        2900

So as you can see I have a total of four segments SEG1, SEG2, SEG3 and SEG4 across both the tables and some segments, risk levels are not present in both the tables. I want minimum of VALUE from both the tables for the combination of SEGMENT and LEVEL columns. Output of the query should be something like this:

SEGMENT       LEVEL        VALUE
----------------------------------
SEG1              1        1000
SEG1              2         900
SEG1              3         800
SEG2              1        3000
SEG2              2        3000
SEG2              3        1000
SEG3              1        5000
SEG3              2        3500
SEG3              3        2200
SEG4              1       10000
SEG4              2        8100
SEG4              3        2900

I tried achieving it through Full outer join but output was not what I expected:

SELECT t1.SEGMENT, t1.LEVEL, t2.SEGMENT, t2.LEVEL, LEAST(t1.VALUE, t2.VALUE)
FROM TAB1 t1
FULL OUTER JOIN TAB2 t2
ON t1.SEGMENT = t2.SEGMENT
AND t1.LEVEL = t2.LEVEL;

Any help is highly appreciated. Database is Oracle.

Upvotes: 3

Views: 19197

Answers (1)

Kamil Ibadov
Kamil Ibadov

Reputation: 710

try this,

select segment, level, min(value) value
from (
  select segment, level, value from tab1
  UNION ALL
  select segment, level, value from tab2
) d
group by segment,level

Upvotes: 4

Related Questions