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
