SQLTeam.com | Weblogs | Forums

How to add a desc | Asc to a order by case statement with multiple columns

order by
 case	     when SortOrder = 1 then MaterialTypeID 
			  when SortOrder = 2 then (MaterialTypeID+ SubjectCode+ FileGrade)

			 when SortOrder = 3 then ((SubjectCode  asc  ) +  FileGrade desc)
			 
    			 when SortOrder = 4 then (MaterialTypeID + FormName + SubjectCode + FileGrade ) else sortorder end 

    Is it possible for the Sortorder = 3 to have an ascending order for SubjectCode but a desc code for filegrade.

You have to break it up into several CASE WHEN.... END expressions. Like shown below:

ORDER BY
	CASE WHEN SortOrder = 1 THEN MaterialTypeId END,
	CASE WHEN SortOrder = 2 THEN MaterialTypeID+ SubjectCode+ FileGrade END,
	CASE WHEN SortOrder = 3 THEN SubjectCode END  ASC,
	CASE WHEN SortOrder = 3 THEN FileGrade END DESC, 
	CASE WHEN SortOrder = 4 THEN MaterialTypeID + FormName + SubjectCode + FileGrade END

Okay that makes senses

Thanks for your help and very quick response. :grinning: