SQL Exercise: Sort employees with minimum salary for a designation

SQL subqueries on employee Database: Exercise-50 with Solution

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

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

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE salary IN
    (SELECT min(salary)
     FROM employees
     GROUP BY job_name)
ORDER BY salary ASC;

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
(7 rows)

Explanation:

The said query in SQL that retrieves all employees from the 'employees' table whose salary is equal to the minimum salary for their job, ordered by salary in ascending order.

The subquery in the WHERE clause selects the minimum salary for each distinct value of "job_name" in the 'employees' table.

The GROUP BY clause is used in the subquery to group the results by "job_name" and select the minimum salary for each group.

The ORDER BY clause sorts the results in ascending 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: Names of the employees and the manager of JONAS.
Next SQL Exercise: DESC order of highest-paid employees by designation.