Oracle SQL - Maximum from two select statements

Question

I have two select statements that find the maximum salary for a specific role. Now I want to have the maximum of the two returns of those select statements. Think about the following code:

SELECT MAX(SALARY) 
  FROM TABLE1 
 WHERE ROLE = 'MANAGER'

SELECT MAX(SALARY) 
  FROM TABLE1 
 WHERE ROLE = 'DEVELOPER'; 

At the end I want the maximum of these two numbers.

How would I do all this in one query?

Have two selects for maximum, then compare those maximums and give the maximum of the two maximums?

Answer 1

If you want maximum from more job titles, add that job title in "IN" array. No need to write one select statement for each job title.

SELECT MAX(SALARY) FROM TABLE1 WHERE ROLE IN ('MANAGER','DEVELOPER')

Answer 2

Since both selects read from the same table, you can do this:

SELECT MAX(SALARY) FROM TABLE1 WHERE ROLE = 'MANAGER' OR ROLE = 'DEVELOPER'

Answer 3

You can figure out the max salary and the role having that salary like this.

SELECT * FROM
(
   SELECT MAX(SALARY) MAXSALARY, ROLE
     FROM TABLE1
    WHERE ROLE IN ('MANAGER','DEVELOPER')
    GROUP BY ROLE
    ORDER BY MAX(SALARY)
)
WHERE ROWNUM = 1

Answer 4

This can be solved with just one select statement which varies on the role.

SELECT MAX(SALARY) FROM TABLE1 WHERE ROLE = 'MANAGER' OR ROLE = 'DEVELOPER'