How do I find the staff with the highest salary in every occupation from the table below (Oracle SQL)

Question

How do I find the staff with the highest salary in every occupation from the table below (Oracle SQL). This is my table below. Any help will be appreciated.

CREATE TABLE STAFF (
    Staff_ID NUMBER(5),
    First_Name VARCHAR2(25),
    Last_Name VARCHAR2(25),
    Date_Of_Birth DATE,
    Gender VARCHAR2(25),
    Email VARCHAR2(25),
    Telephone NUMBER(15),
    Department VARCHAR2(25),
    Staff_Type VARCHAR2(25),
    Visa_Restrictions VARCHAR2(40),
    Occupation VARCHAR2(25),
    Hire_Date DATE,
    Salary NUMBER (10),
    CONSTRAINT PK_STAFF PRIMARY KEY (Staff_ID)
 );

@NickW I tried this; ``` SELECT * FROM staff WHERE salary IN (SELECT MAX(Salary) FROM Staff); ``` Its returning only one record, that's the problem — Solomon, May 06 '23 at 13:45
SELECT * FROM staff WHERE (occupation, salary) IN (SELECT occupation, MAX(Salary) FROM Staff group by occupation). Or use row_number() or rank(). — Ponder Stibbons, May 06 '23 at 14:28

score 1 · Accepted Answer · answered May 06 '23 at 16:28

Code you posted does the job and returns correct result. Though, you're accessing the staff table twice, so - performance wise - I guess it could be improved. Here's one option: use rank analytic function which ranks rows for each occupation by salary in descending order, and then retrieve rows that ranked as the highest. As this query accesses the table only once, it should perform better if you have to deal with a lot of rows.

For sample data:

SQL> select * from staff order by occupation, salary desc;

ENAME      OCCUPATIO     SALARY
---------- --------- ----------
FORD       ANALYST         3225
SCOTT      ANALYST         3225
MILLER     CLERK           1365
ADAMS      CLERK         1182.5
JAMES      CLERK           1045
SMITH      CLERK            860
JONES      MANAGER      3198.13
BLAKE      MANAGER         3135
CLARK      MANAGER       2572.5
KING       PRESIDENT       5250
ALLEN      SALESMAN        1760
TURNER     SALESMAN        1650
MARTIN     SALESMAN        1375
WARD       SALESMAN        1375

14 rows selected.

Query:

SQL> with temp as
  2    (select s.*,
  3       rank() over (partition by s.occupation order by s.salary desc) rnk
  4     from staff s
  5    )
  6  select t.ename, t.occupation, t.salary
  7  from temp t
  8  where t.rnk = 1
  9  order by t.occupation;

ENAME      OCCUPATIO     SALARY
---------- --------- ----------
SCOTT      ANALYST         3225
FORD       ANALYST         3225
MILLER     CLERK           1365
JONES      MANAGER      3198.13
KING       PRESIDENT       5250
ALLEN      SALESMAN        1760

6 rows selected.

SQL>

score 0 · Answer 2 · edited May 06 '23 at 15:15

0

SELECT s1.Occupation, s1.First_Name, s1.Last_Name, s1.Salary
FROM STAFF s1
WHERE s1.Salary = (
    SELECT MAX(s2.Salary)
    FROM STAFF s2
    WHERE s2.Occupation = s1.Occupation
)
ORDER BY s1.Occupation ASC;

edited May 06 '23 at 15:15

nbk

45,398
8
30
47

answered May 06 '23 at 15:00

Solomon

71
5

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney May 07 '23 at 01:26

How do I find the staff with the highest salary in every occupation from the table below (Oracle SQL)

2 Answers2