sql_language thread: Syntax check error with SELECT DISTINCT if a CASE is in the ORDER BY
I have a SELECT statement which includes a CASE in the ORDER BY. It works
fine unless I try to add DISTINCT to the statement. When I add DISTINCT,
MSSQL's syntax checker wont let me close and save the stored procedure.
Instead it throws up an error dialog saying that 'ORDER BY items must
appear in the select list if SELECT DISTINCT is specified'...in which they
do appear. It seems like the syntax checker isn't able to parse the CASE
statement in the ORDER BY.
SELECT DISTINCT
auth.lastName, mjcit.MedlineTA
FROM
citation cit
WHERE
cit.WFStatus >=40
ORDER BY
CASE @sort
WHEN '2' THEN auth.lastName
WHEN '3' THEN mjcit.MedlineTA
END ASC
Any ideas much appreciated,
NAME REMOVED