The HR director at Helix Systems is reviewing the full hiring history for an annual headcount audit.
Write a query to return each employee's name and job title, ordered from the most recently hired to the earliest.
Assumptions:
- The
employeestable contains every active and former employee at Helix Systems. - When two employees share a
hire_date, the employee with the loweridshould appear first.
Output:
- One row per employee, with columns
nameandtitleonly, sorted byhire_datedescending, then byidascending.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
name,
title
FROM
employees
ORDER BY
hire_date DESC,
idThe shape
ORDER BY hire_date DESC sorts the result by a column that never appears in the output. hire_date lives on employees, so it's available to the sort even though only name and title are projected.
Clause by clause
SELECT name, titlereturns only the two columns the audit needs displayed: who the employee is and what their job title is.hire_dateis deliberately excluded from the output — the prompt's output spec listsnameandtitleonly.FROM employeesreads the full employee table, including every column on each row. The sort has access to every column on the source rows, not just the ones being projected.ORDER BY hire_date DESC, idsorts descending byhire_dateso the most recent hire lands on top, then breaks ties byidascending. Both sort keys reference columns onemployeesthat don't appear in theSELECTlist, and that's fine —ORDER BYoperates on the source rows the query has fetched, not on the columns it has chosen to project.
Why this and not adding hire_date to the SELECT
Adding hire_date to the output would change the result shape: three columns instead of two, with the date visible in every row. The audit doesn't need that — it needs the employee list in hire-date order, with the date itself implicit in the ordering. Sorting by a column the output doesn't show is how you express "order matters, the value doesn't."
The execution order is what makes this work. FROM reads the table, SELECT chooses which columns to project, and ORDER BY runs after both. By the time the sort happens, every column on the source row is still in scope; the projection happens last and only affects what comes back to the client.
The trap
The instinct to add hire_date to the SELECT list to make the ordering visible is the trap, and it bites silently. The query still returns the right rows in the right order, but it returns them with an extra column the prompt's output spec doesn't ask for. In a grader that checks column count, that's an immediate fail; in a real audit, it's an unnecessary column the recipient has to delete before circulating the report. Sort by what the ordering needs, project what the output needs, and don't let the two lists drift into each other.
You practiced sorting by a column that isn't in the SELECT list. ORDER BY operates on the source rows, not the projected output, so any column on the underlying table can serve as a sort key — the recurring shape any time the ordering criterion is a different attribute from what you want to display.
