How to handle ties in SQL

Question

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!

Answer 1

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
    )
)

Answer 2

You may use the RANK analytic function here:

WITH cte AS (
    SELECT e.*, RANK() OVER (PARTITION BY department_id
                             ORDER BY hire_date DESC, employee_id) rnk
    FROM employees e
)

SELECT employee_id, department_id, first_name, last_name, hire_date, job_id
FROM cte
WHERE rnk = 1;