Schema and sample data
| product_id | name | category | price |
|---|---|---|---|
| 1 | Trail Runner Shoe | Footwear | 89.00 |
| 2 | Canvas Sneaker | Footwear | 59.00 |
| 3 | Wool Beanie | Accessories | 19.00 |
| 4 | Leather Belt | Accessories | 35.00 |
| 5 | Rain Jacket | Outerwear | 120.00 |
| 6 | Down Vest | Outerwear | 98.00 |
| 7 | Cotton T-Shirt | Apparel | 25.00 |
| 8 | Ankle Sock | Footwear | 9.00 |
Show setup SQL (copy to run)
CREATE TABLE Products (
product_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
INSERT INTO Products (product_id, name, category, price) VALUES
(1, 'Trail Runner Shoe', 'Footwear', 89.00),
(2, 'Canvas Sneaker', 'Footwear', 59.00),
(3, 'Wool Beanie', 'Accessories', 19.00),
(4, 'Leather Belt', 'Accessories', 35.00),
(5, 'Rain Jacket', 'Outerwear', 120.00),
(6, 'Down Vest', 'Outerwear', 98.00),
(7, 'Cotton T-Shirt', 'Apparel', 25.00),
(8, 'Ankle Sock', 'Footwear', 9.00);The Products table lists many products, and several of them share the same category. Return each distinct category exactly once, with no duplicates. Sort the result by category in alphabetical (ascending) order.
Expected result
| category |
|---|
| Accessories |
| Apparel |
| Footwear |
| Outerwear |
Show hint
Selecting the category column on its own would repeat every value once per product row. Add SELECT DISTINCT to collapse the repeats into one row per category, then ORDER BY to put them in alphabetical order.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT DISTINCT category
FROM Products
ORDER BY category;SELECT DISTINCT tells SQL Server to remove duplicate rows from the result. Because we select only the category column, "duplicate" means "same category value", so the four categories that appear across the eight products (Footwear appears three times, Accessories and Outerwear twice each, Apparel once) collapse into four single rows.
ORDER BY category then sorts those distinct values alphabetically. The default sort direction is ascending, so no ASC keyword is needed. Note that ORDER BY runs after the duplicates are removed, so you are sorting the four surviving rows rather than all eight.
A common alternative is GROUP BY category with no aggregate function, which produces the same distinct list. Both are valid, but reach for DISTINCT when you just want unique values and GROUP BY when you also need aggregates such as COUNT per category. Avoid sprinkling DISTINCT onto every query to "fix" unexpected duplicates: often the real cause is a join that needs a better condition.