Eray Balkanli Eray Balkanli - 6 months ago 15
SQL Question

Order by NULL while select distinct is used

Sorry if it is already asked, but I searched and couldnt see the solution. I have a query in MSSQL Server 2008 like:

Select Distinct ProcedureId. , UserId , FacilityId, LocationId
From ....
Where ....
order by case when @sortByProcedure = 0 then null else 1 end,
2,
3,
4


This query is giving an error:


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


It is happening because of the case..when clause, when I remove it, it is working. To what I should replace null there to make it work? Any help would be appreciated.

Thanks.

Answer

You need to include that case statement in the select to be able to sort by it;

SELECT DISTINCT
CASE WHEN @sortByProcedure = 0 THEN NULL ELSE 1 END SortField
, ProcedureId 
, UserId 
, FacilityId
, LocationId
FROM ....
WHERE ....
ORDER BY 
CASE WHEN @sortByProcedure = 0 THEN NULL ELSE 1 END
, ProcedureId
, UserId
, FacilityId
, LocationId