CODEWITHSUNDEEP
sqloracle-databaseselectmaxwhere-clause
oneiros
oneiros

Reputation: 3568

Oracle SQL - Maximum from two select statements

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?

Upvotes: 5

Views: 829

Answers (4)

user1073456
user1073456

Reputation:

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'

Upvotes: 3

Somnath Muluk
Somnath Muluk

Reputation: 57656

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')

Upvotes: 10

O. Jones
O. Jones

Reputation: 108641

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

Upvotes: 4

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

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

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

Upvotes: 9

Related Questions