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

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

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>

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;