SQL Exercise: DESC order of highest-paid employees by designation

SQL subqueries on employee Database: Exercise-51 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

51. From the following table, write a SQL query to find those employees who receive maximum salary for a designation. Sort the result-set in descending order by salary. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE salary IN
    (SELECT max(salary)
     FROM employees
     GROUP BY job_name)
ORDER BY salary DESC;

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
(6 rows)

Explanation:

The said query in SQL that returns all the employees from the 'employees' table who have the highest salary for their respective job, sorted in descending order by salary.

The subquery finds the maximum salary for each job_name in the employees table.

The outer query then selects all the rows from the employees table where the salary matches the maximum salary for their respective job name. The ORDER BY clause sorts the results in descending order by salary.

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Sort employees with minimum salary for a designation.
Next SQL Exercise: Recent hires in every department in order of hire date.