SQL Server Errors: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

SQL Server force you to put columns in SELECT DISTINCT list which are part of ORDER BY clause. But what if we don’t want to add that column/s in SELECT list. Lets try it.

--create temporary table to hold records

CREATE TABLE #DistinctSortTest (Val1 INT, Val2 INT)

GO

--insert some records

INSERT INTO #DistinctSortTest

VALUES (1,100),(8,55),(3,33),(1,1),(9,999)

GO

--lets see what we have in table

SELECT * FROM #DistinctSortTest

GO

 
From this table we need only “Val1” column with distinct values BUT sorting output with “Val2”. Lets try simple query.

SELECT DISTINCT Val1

FROM #DistinctSortTest

ORDER BY Val2

Opps. Error

Msg 145, Level 15, State 1, Line 1

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

To resolve this problem, we can use GROUP BY clause with MIN()or MAX() function in ORDER BY clause. 

SELECT  Val1

FROM #DistinctSortTest

GROUP BY Val1

ORDER BY MIN(Val2)

Through GROUP BY (all select columns) we will achive functionality of DISTINCT and MIN()/MAX() functions for sorting. MIN() in Order By clause can be used for ASC sort and MAX() for DESC sort.

--drop temporary table when not required

DROP TABLE #DistinctSortTest