Using 'case' in ORDER BY (MySQL) -

i'm working in procedure returns huge select wan't define how order. wan't choose 1 of 3 fields , if ascending or descending, if none of 3 options define, returns default first field in descending

this way

order  case option1      when 0                          case option2              when 0 firstfield desc             when 1 firstfield asc         end      when 1                      case option2              when 0 secondfield desc             when 1 secondfield asc         end      when 2                      case option2              when 0 thirdfield desc             when 1 thirdfield asc         end                      else          firstfield desc     end  end; 

of course, didn't work... mysql acuses errors in words 'desc' , 'asc', how can make works??

in order this, have move desc/asc outside case come after expression.

you should separate fields. in case statement if fields have different datatypes, converted 1 fits (usually varchar) , can mess ordering.

you like:

order case when option1=0 , option2=0 firstfield end desc,           case when option1=0 , option2=1 firstfield end asc,           case when option1=1 , option2=0 secondfield end desc,          case when option1=1 , option2=1 secondfield end asc,          case when option1=2 , option2=0 thirdfield end desc,          case when option1=2 , option2=1 thirdfield end asc,          firstfield desc  

each case return null rows when not applicable , have same value. in fact first case unnecessary caught default, have included clarity. may result in hugely expensive ordering process however!

i think i'd rather dynamically build sql , i'd use array of allowed ordering values keep secure.