You can pull the right rows now. Three small clauses control how the result looks: ORDER BY sorts it, LIMIT trims it, and DISTINCT removes duplicates.
ORDER BY sorts the output (add DESC for high-to-low), LIMIT keeps only the first N rows, and DISTINCT collapses duplicate rows into one.
ORDER BY — sort the result
A query's rows come back in no guaranteed order unless you ask. ORDER BY fixes that. Run it, then add DESC after salary to flip it to highest-first:
You can sort by more than one column: ORDER BY department, salary DESC sorts by department first, then by salary within each department.
LIMIT — just the top rows
Pair ORDER BY with LIMIT to answer "top N" questions. Run it for the two highest earners:
Watch out
LIMIT without ORDER BY gives you some rows, but not predictably the ones you mean. "Top 2 earners" needs the sort; otherwise you just get whichever 2 rows the database hands back first.
DISTINCT — remove duplicate rows
DISTINCT collapses identical rows into one. It's the quickest way to answer "what are the unique values?" Here the five department values become three: each repeat after the first is dropped.
| department |
|---|
| Engineering |
| Sales |
| Engineering |
| Sales |
| Marketing |
DISTINCT department| department |
|---|
| Engineering |
| Sales |
| Marketing |
Try it now: write the query that produces the distinct list above, returning each department once.
Your turn · Exercise
Return the distinct list of departments (each department once).
The data
employees
5 rows returned
| name | department |
|---|---|
| Ada | Engineering |
| Bo | Sales |
| Cy | Engineering |
| Di | Sales |
| Eli | Marketing |
Run it to list each department once:
DISTINCT applies to the whole row you selected, not one column. SELECT DISTINCT department, city gives unique combinations of department and city, which may surprise you if you expected unique departments alone.
From practice
When a list looks "too long" or has obvious repeats, SELECT DISTINCT is the fastest sanity check. But for counting uniques later you'll use COUNT(DISTINCT col); that's coming in the aggregation lesson.
Your turn
Combine ORDER BY and LIMIT. Return the three highest-paid employees: name and salary, highest first.
Your turn · Exercise
Return the three highest-paid employees: name and salary, highest salary first.
The data
employees
5 rows returned
| name | salary |
|---|---|
| Ada | 62,000 |
| Bo | 48,000 |
| Cy | 71,000 |
| Di | 52,000 |
| Eli | 45,000 |
Next: filtering with more than one condition. Ranges, lists, text patterns, and the tricky case of missing values.