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 2900So 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 2900I 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.
This Oracle tutorial explains how to use the Oracle/PLSQL MIN function with syntax and examples.
The Oracle/PLSQL MIN function returns the minimum value of an expression.
The syntax for the MIN function in Oracle/PLSQL is: SELECT MIN(aggregate_expression) FROM tables [WHERE conditions];OR the syntax for the MIN function when grouping the results by one or more columns is: SELECT expression1, expression2, ... expression_n, MIN(aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;Parameters or Argumentsexpression1, expression2, ... expression_n Expressions that are not encapsulated within the MIN function and must be included in the GROUP BY clause at the end of the SQL statement. aggregate_expression This is the column or expression from which the minimum value will be returned. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the records to be selected.
The MIN function returns the minimum value.
The MIN function can be used in the following versions of Oracle/PLSQL:
Let's look at some Oracle MIN function examples and explore how to use the MIN function in Oracle/PLSQL. For example, you might wish to know how the minimum salary of all employees. SELECT MIN(salary) AS "Lowest Salary" FROM employees;In this MIN function example, we've aliased the MIN(salary) expression as "Lowest Salary". As a result, "Lowest Salary" will display as the field name when the result set is returned.
In some cases, you will be required to use the GROUP BY clause with the MIN function. For example, you could also use the MIN function to return the name of the department and the minimum salary in the department. SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department;Because you have listed one column in your SELECT statement that is not encapsulated in the MIN function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.
This Oracle tutorial explains how to use the Oracle/PLSQL LEAST function with syntax and examples.
The Oracle/PLSQL LEAST function returns the smallest value in a list of expressions.
The syntax for the LEAST function in Oracle/PLSQL is: LEAST( expr1 [, expr2, ... expr_n] )Parameters or Argumentsexpr1 The first expression to evaluate whether it is the smallest. expr2, ... expr_n Optional. Additional expressions that are to be evaluated.
The LEAST function returns a value that is the same datatype as expr1. Note
The LEAST function can be used in the following versions of Oracle/PLSQL:
Let's look at some Oracle LEAST function examples and explore how to use the LEAST function in Oracle/PLSQL. For example: LEAST(2, 5, 12, 3) Result: 2 LEAST('2', '5', '12', '3') Result: '12' LEAST('apples', 'oranges', 'bananas') Result: 'apples' LEAST('apples', 'applis', 'applas') Result: 'applas' LEAST('apples', 'applis', 'applas', null) Result: NULLfor v_rec in a loop (select sal from emp) If nvl (v_min_sal, v_rec.sal + 1) > v_rec.sal dbms_output.put_line (' lowest salaray is "' |") v_min_sal | '"'); The lowest Salaray is "800". PL/SQL procedure successfully completed.
|