ORDER BY & Sorting SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Apply a Custom Department Priority

Return first_name, last_name, department_id, and salary using the requested custom department priority, then salary descending within each priority.

  • CASE expressions
  • Sorting

Exercise brief

Understand the request

Incident staffing lead An incident roster must show IT first, Sales second, and HR last regardless of numeric department IDs.

List employees in a custom department priority: IT first, then Sales, then HR. Within each group, sort by salary descending. Show first_name, last_name, department_id, salary.

Return

  • Order department 1 first, department 3 second, and department 2 last.
  • Order higher salaries first within a department.

Constraints

  • Use CASE in ORDER BY for the custom priority.
  • Use salary DESC as the secondary key.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • first_nameTEXT
  • last_nameTEXT
  • department_idINTEGER
  • salaryDECIMAL

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

A numeric CASE inside ORDER BY lets you express any ranking — the lower the number, the earlier the group appears.

Hint 2

You can use a simple CASE form (`CASE department_id WHEN 1 THEN ...`) or a searched CASE (`CASE WHEN department_id = 1 THEN ...`). Both work.

Hint 3

ORDER BY CASE department_id WHEN 1 THEN 1 WHEN 3 THEN 2 ELSE 3 END, salary DESC

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT first_name, last_name, department_id, salary FROM employees ORDER BY CASE department_id WHEN 1 THEN 1 WHEN 3 THEN 2 ELSE 3 END, salary DESC;

Why this works

CASE-in-ORDER-BY is the standard recipe for non-alphabetic, non-numeric ordering — priority lists, status workflows, urgency levels, etc. Once the rank value is computed, the engine sorts numerically.

Success check

Rows follow the business priority rather than the natural department_id order.

Expected result

Use this output to verify values, aliases, ordering, and row count.

first_namelast_namedepartment_idsalary
MikeJohnson180000
AmyTaylor178000
JohnDoe175000
AlexMiller172000
DavidBrown170000
ChrisAnderson368000
SarahWilliams365000
RachelGarcia364000
TomWilson362000
LisaDavis258000

Previewing 10 of 12 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.