Solving tips
- Use SELECT DISTINCT to collapse repeated values into a unique set.
- DISTINCT applies to the whole selected row, so list only the column you want deduplicated.
- Add ORDER BY when the task requires a stable, predictable ordering of the results.
You are given a product catalog where many products share a category. Produce the list of unique categories for a filter menu.
Schema
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
category TEXT,
price NUMERIC(10,2)
);
Sample data:
| id | name | category | price |
|---|
| 1 | Laptop | Electronics | 1200.00 |
| 2 | Mouse | Electronics | 25.00 |
| 3 | Desk | Furniture | 300.00 |
| 4 | Chair | Furniture | 150.00 |
| 5 | Notebook | Stationery | 5.00 |
| 6 | Monitor | Electronics | 400.00 |
| 7 | Pen | Stationery | 2.00 |
Task
Return each distinct category exactly once, with the column aliased as category. Order the results alphabetically in ascending order.
Expected output
| category |
|---|
| Electronics |
| Furniture |
| Stationery |
Approach
Deduplication of a single column is exactly what SELECT DISTINCT is for. Select category with DISTINCT to remove repeats, then apply ORDER BY category to guarantee the alphabetical ordering the task requires.
Query
SELECT DISTINCT category
FROM products
ORDER BY category ASC;
Walkthrough
The raw category column holds Electronics, Electronics, Furniture, Furniture, Stationery, Electronics, Stationery. DISTINCT folds those seven values into three unique ones: Electronics, Furniture, and Stationery. ORDER BY category ASC then sorts them alphabetically, which they already happen to be, producing the three-row result.
Complexity & notes
DISTINCT typically requires a sort or hash aggregate over the column, so it is O(n log n) or O(n) respectively. Remember that DISTINCT acts on the entire selected row: SELECT DISTINCT category, price would return category/price pairs, not unique categories. For this single-column case, GROUP BY category returns the same result and is equivalent. NULL categories, if present, would collapse to a single row because DISTINCT treats all NULLs as one group.