Multiple ORDER BY in CASE

Question

I have a select statement that ends with the following:

order by case @pcsort when '' then compcode asc else received asc, compcode asc end 

Basically, I need this if @pcsort is '' and then the order is compcode , otherwise the order received and compcode in that order.

Any ideas?

Answer 1

This will do what you want if the data types are supposed to be compatible.

 order by case @pcsort when '' then compcode else received end ASC, compcode ASC 

More generally, you need one CASE column for each class, assuming data type compatibility

 order by case @pcsort when '' then compcode else received end ASC, case @pcsort --safe to sort on same column agaon , or use a constant when '' then compcode or <constant of same type as compcode> else compcode end ASC 

If the data types are incompatible, you need more cases and many constants

 order by case @pcsort when '' then compcode else <constant of same type as compcode> end ASC, case @pcsort when '' then <constant of same type as received> else received end ASC, case @pcsort when '' then <constant of same type as compcode> else compcode end ASC