SQL Exercise: Recent hires in every department in order of hire date

SQL subqueries on employee Database: Exercise-52 with Solution

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

52. From the following table, write a SQL query to find recently hired employees of every department. Sort the result-set in descending order by hire date. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees e
WHERE hire_date IN
    (SELECT max(hire_date)
     FROM employees
     WHERE e.dep_id = dep_id )
ORDER BY hire_date DESC;

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001

Explanation:

The said query in SQL that selects all the employees who were hired most recently in their respective departments, sorted in descending order by hire_date.

The subquery that returns the maximum hire date for the department that each employee belongs to.

The outer query then selects all the rows from the employees table where the hire_date matches the maximum hire date for their respective department. The ORDER BY clause sorts the results in descending order by hire_date.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: DESC order of highest-paid employees by designation.
Next SQL Exercise: Employees salary exceed department average, ASC order.