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.