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

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿