How to handle ties in SQL

March 28, 2022

SELECT
    employee_id,
    department_id,
    first_name,
    last_name,
    hire_date,
    job_id
FROM employees e
WHERE hire_date IN 
     (
         SELECT max(hire_date)
         FROM employees
         WHERE e.department_id = department_id 
     )
ORDER BY hire_date ASC

Result of the query

So this is my query and the result of it. There are two tied results for SA_REP department and I should have only one result – for instance the one with the lower employee_id. I’ve googled the problem but can’t seem to find any related results…

Thanks for any help!

>Solution :

You can use a not exists query which gives you more flexibility:

SELECT *
FROM employees e
WHERE NOT EXISTS (                                                  -- no x exists that...
    SELECT *
    FROM employees x
    WHERE x.department_id = e.department_id                         -- belongs to same department 
    AND (
        x.hire_date > e.hire_date OR                                -- but hired later than e
        x.hire_date = e.hire_date AND x.employee_id < e.employee_id -- hired on same date but has lesser employee id than e
    )
)