DISTINCT on Multiple Columns
Return every unique department_id and job_id combination.
- Sorting
- Distinct values
Exercise brief
Understand the request
Workforce taxonomy analyst The planning model needs each department and job pairing only once.
Return every unique combination of department_id and job_id that exists in the employees table, sorted by department_id then job_id.
Return
- Return department_id and job_id.
Constraints
- Remove duplicate projected pairs.
- Sort by department_id, then job_id.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
department_idINTEGERjob_idVARCHAR(20)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
DISTINCT operates on the whole projected row. So `SELECT DISTINCT a, b` returns each unique (a, b) pair, not each unique a and each unique b separately.
Hint 2
Combine DISTINCT with ORDER BY to get a deterministic order in the output.
Hint 3
Apply DISTINCT to the two-column projection and use both columns as deterministic sort keys.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT DISTINCT department_id, job_id FROM employees ORDER BY department_id, job_id;Why this works
A common confusion is reading `SELECT DISTINCT a, b` as "distinct values of a, plus all b". It is not — DISTINCT collapses identical full rows. Two rows with the same (a, b) are merged; rows that differ in either column are kept.
Success check
Each observed department_id and job_id pair appears exactly once in the requested deterministic order.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| department_id | job_id |
|---|---|
| 10 | IT_MGR |
| 10 | IT_PROG |
| 20 | HR_REP |
| 30 | FIN_ANALYST |
| 40 | MKT_MGR |
| 40 | SALES_REP |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
Basic SQL Functions
Practice production-oriented string cleanup, numeric transformations, NULL handling, tolerant conversion, and delimiter parsing.
ORDER BY & Sorting
Practice deterministic SQL ordering with tie-breakers, custom priorities, NULL placement, expressions, joined data, aggregates, and portable top-N patterns.
Open the interactive workspace and practice across SQL topics.