Wrox Home  
Search P2P Archive for: Go

  Return to Index  

sql_language thread: Syntax check error with SELECT DISTINCT if a CASE is in the ORDER BY


Message #1 by "<NAME REMOVED>" <EMAIL REMOVED...> on Wed, 9 Oct 2002 01:34:17
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

  Return to Index