Wednesday 18 September 2013

Max number existing in any of the participating table columns

Question - There are two tables (Ex - test1 & test2 ) having only one column of number type (Ex. number1 & number2). Write a query to find out max number existing in any of these tables.

Sample data setup query - 

CREATE TABLE test1( number1 NUMBER);
insert into test1 values (21);
INSERT INTO test1 VALUES (22);
INSERT INTO test1 VALUES (23);
INSERT INTO test1 VALUES (24);
insert into test1 values (25);


CREATE TABLE test2( number2 NUMBER);
INSERT INTO test2 VALUES (211);
INSERT INTO test2 VALUES (221);
INSERT INTO test2 VALUES (231);
INSERT INTO test2 VALUES (241);
insert into test2 values (251);

Three possible solutions - 

SELECT greatest((SELECT MAX(number1) FROM test1), (SELECT MAX(number2) FROM test2)) MAX FROM dual;

SELECT CASE WHEN (MAX(number1)>MAX(number2))
            THEN MAX(number1)
            ELSE
            MAX(number2)
            END CASE
from test1,test2;

SELECT MAX(number3) FROM 
(
  (SELECT MAX(number1)number3 FROM test1)
  UNION
  (SELECT MAX(number2)number3 FROM test2)
);

No comments:

Post a Comment