Level 7 · Debug: the newest hires list is wrong

Nothing in this query errors, and it still returns five people with titles and dates. The bug is in two characters: 4 and 1.

What a number in ORDER BY means

ORDER BY 4 sorts by the fourth column of the SELECT list, whatever that column is. The original query selected employee_id, first_name, last_name, hire_date, so 4 was the hire date. Then title was added before hire_date, the SELECT list shifted, and 4 now points at title. Sorting titles from Z to A puts the Warehouse Manager first, then the Support Team Lead and three Support Specialists, regardless of when they joined.

The fix is to name what you sort by:

SELECT employee_id, first_name, last_name, title, hire_date
FROM employees
ORDER BY hire_date DESC, employee_id DESC
LIMIT 5;

The list now starts with Helen Parker (employee 36) and Charles Ruiz (employee 21), both hired on 2025-10-31. The tie-breaker employee_id DESC puts 36 before 21, and every run returns the same five.

How to spot it

• The sort column in the result is not in order. Here hire_date jumps around, while title is perfectly sorted. That tells you which column the database actually used.
• The query was recently edited, and only the SELECT list changed. Positional references are the first suspect.
• The ticket says “it used to work”. Something changed, so compare the old and new versions line by line before rewriting anything.

Common mistakes

• Replacing 4 with 5. Correct today, broken again after the next added column.
• Removing the tie-breaker. Two people share the newest hire date, so without it their order is not guaranteed.
• Fixing the ORDER BY but not the tie-breaker. 1 DESC still points at employee_id today, but it has the same weakness: name it, employee_id DESC, so the next edit to the SELECT list cannot move it either. Named columns also make the query readable without counting.

In SQL Server

Positional ORDER BY behaves the same there, so the same fix applies. The top five uses TOP:

SELECT TOP 5 employee_id, title, hire_date
FROM employees
ORDER BY hire_date DESC, employee_id DESC;