Sort Within Department Groups
Return first_name, last_name, department_id, and salary, ordered by department_id ascending and salary descending within each department.
- Sorting
Exercise brief
Understand the request
Workforce planning manager A staffing report groups employees by department and ranks pay within each group.
List employees sorted first by department_id ascending, then by salary descending within each department. Show first_name, last_name, department_id, salary.
Return
- Keep each department together.
- Rank higher salaries first within a department.
Constraints
- Use two ORDER BY keys with independent directions.
- department_id must be the primary key and salary the secondary key.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXTdepartment_idINTEGERsalaryDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
List multiple columns in ORDER BY separated by commas. The first is the PRIMARY sort, the next breaks ties, and so on.
Hint 2
ASC/DESC attaches to ONE column. `ORDER BY a, b DESC` = a ASC, b DESC. To sort BOTH descending: `ORDER BY a DESC, b DESC`.
Hint 3
ORDER BY department_id, 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 department_id, salary DESC;Why this works
The secondary sort only matters when the primary sort has ties. For 12 employees in 3 departments, you will see clear blocks: department 1 listed in salary-DESC, then department 2, then department 3.
Success check
Departments appear in numeric order and every department block is salary-ranked.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| first_name | last_name | department_id | salary |
|---|---|---|---|
| Mike | Johnson | 1 | 80000 |
| Amy | Taylor | 1 | 78000 |
| John | Doe | 1 | 75000 |
| Alex | Miller | 1 | 72000 |
| David | Brown | 1 | 70000 |
| Lisa | Davis | 2 | 58000 |
| Jane | Smith | 2 | 55000 |
| Emma | Thomas | 2 | 52000 |
| Chris | Anderson | 3 | 68000 |
| Sarah | Williams | 3 | 65000 |
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
LIMIT & OFFSET
Practice deterministic top-N, cutoff ties, offset pagination, composite keyset cursors, and resumable bounded batches.
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
Open the interactive workspace and practice across SQL topics.