Order BY items must appear in the select list if SELECT DISTINCT is specified

Dealing with Ambiguous Columns

Did your SQL throw a fit? If column names lead to confusion or errors, chill it out with column positions.

-- Sorry column name, SQL has chosen column number 1.
SELECT DISTINCT column1, column2 FROM table ORDER BY 1, 2;


Handling Calculated Fields

Does your DISTINCT have a frenemy relationship with calculated fields? Kick out DISTINCT and invite GROUP BY for a more harmonious party.

-- GROUP BY = DISTINCT + plus smooth handling of calculated fields
SELECT column, COUNT(*) as freq FROM table GROUP BY column ORDER BY freq;


Concatenate to Sort

When sorting gets tough, remember, you can do the column fusion dance (aka concatenation).

-- Column fusion dance = column1 + column2
SELECT DISTINCT column1 || column2 as combined_col FROM table ORDER BY combined_col;


Working with Stored Procedures

If you're trapped inside a stored procedure, watch out for additional sorting logic that might ghost your ORDER BY clause.

Sorting out Troubles

If a wild error appears, time to play detective. Test different column/alias combinations and verify tables/aliases.

When to Stick with Position Numbers in ORDER BY

Are your column names causing trouble? Send them into hiding and let the column positions take the stage.

-- When column names pull a disappearing act, call in column numbers.
SELECT DISTINCT column1, column2 FROM table ORDER BY 1, 2;
