Schema and sample data
| emp_id | name | department |
|---|---|---|
| 1 | Ava Torres | Engineering |
| 2 | Ben Ncube | Sales |
| 3 | Cara Singh | Engineering |
| 4 | Deng Liu | Support |
| 5 | Emma Rossi | Sales |
| 6 | Farah Khan | Marketing |
| 7 | Gil Owens | Support |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(30)
);
INSERT INTO Employees (emp_id, name, department) VALUES
(1, 'Ava Torres', 'Engineering'),
(2, 'Ben Ncube', 'Sales'),
(3, 'Cara Singh', 'Engineering'),
(4, 'Deng Liu', 'Support'),
(5, 'Emma Rossi', 'Sales'),
(6, 'Farah Khan', 'Marketing'),
(7, 'Gil Owens', 'Support');The Employees table lists staff, and many employees share the same department. Return every distinct department exactly once, sorted alphabetically in ascending order. The result should have a single column, department.
Expected result
| department |
|---|
| Engineering |
| Marketing |
| Sales |
| Support |
Show hint
Because departments repeat, a plain SELECT department would return one row per employee. Use SELECT DISTINCT to collapse the duplicates, then add an ORDER BY to put the remaining values in alphabetical order.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT DISTINCT department
FROM Employees
ORDER BY department ASC;SELECT DISTINCT removes duplicate rows from the result. There are seven employees but only four unique department values, so the duplicated Engineering, Sales and Support rows collapse into a single row each. DISTINCT applies to the whole selected row, so with one column it deduplicates on that column alone.
ORDER BY department ASC then sorts the four surviving values alphabetically. ASC is the default direction, so ORDER BY department on its own would give the same result, but stating it makes the intent explicit. String sorting follows the column collation, which for the default case insensitive collation orders Engineering, Marketing, Sales, Support.
A common mistake is to reach for GROUP BY department instead. That also produces one row per distinct department and is useful when you need an aggregate such as a count per group, but when you only want the unique values themselves DISTINCT states the intent more directly. Once you do need a per group count, see COUNT.