Why ORDER BY Doesn't Work the Way You Expect with DISTINCT
The ORDER BY clause does not work with DISTINCT in that way. When you write
SELECT DISTINCT col FROM t ORDER BY other_col, SQL rejects it —
you can only order by columns that appear in the SELECT list when DISTINCT
is used. This is a common pitfall when you want unique values from one column
sorted by another.
The Simple Case: Order by the Selected Column
If you want to order the results by the name column, you should simply use:
SELECT * FROM role ORDER BY name;Distinct Values, Ordered by the Same Column
If you want to get distinct role names (though in this case, they are already unique), you can use:
SELECT DISTINCT name FROM role ORDER BY name;
This works because name is both selected and ordered — with
DISTINCT, the ORDER BY columns must be part of the result set.
What Fails and Why
-- ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
SELECT DISTINCT name FROM role ORDER BY id;
Ordering by id is meaningless here anyway: a given name may map
to many ids, so there is no single "correct" order. When you truly need the
earliest or most recent value per name, use GROUP BY with an
aggregate:
SELECT name, MIN(id) FROM role GROUP BY name ORDER BY MIN(id);Related Articles
