sql 2008 order by

Select id, (a*b) as result1, (c*d) as result2 from [table]
group by id
order by
case when @o in (5,6,7,8) then id end,
case when @o=1 then result1 end,
case when @o=2 then (a*b) end desc,
case when @o=3 then (c*d) end,
case when @o=4 then (c*d)end desc

use like this:-

Select id, (a*b) as result1, (c*d) as result2 from table
group by id
order by case @o  when 1 then result1
              case          when 2 then (a*b)  desc,
              case         when 3 then (c*d) ,
              case         when 4 then (c*d) desc,
              case         when 5 then id,(a*b)   ,
              case        when 6 then id,(a*b)    desc,
              case          when 7 then id,(c*d)  ,
              case          when 8 then id,(c*d)  desc
end

Select allOpen in new window

You cannot order by a calculated column name. You must use the calculation in the order.

Select id, (a*b) as result1, (c*d) as result2 from table
group by id
order by case  when @o=1 then result1 <<<< this is wrong
              case          when @o=2 then (a*b)  end desc,
              case         when @o=3 then (c*d) end,
              case         when @o=4 then (c*d)end desc,
              case         when @o=5 then id,(a*b)   end,
              case        when @o=6 then id,(a*b)   end desc,
              case          when @o=7 then id,(c*d)  end,
              case          when @o=8 then id,(c*d)  end desc
 

it should be:

Select id, (a*b) as result1, (c*d) as result2 from table
group by id
order by case  when @o=1 then (a*b)
              case          when @o=2 then (a*b)  end desc,
              case         when @o=3 then (c*d) end,
              case         when @o=4 then (c*d)end desc,
              case         when @o=5 then id,(a*b)   end,
              case        when @o=6 then id,(a*b)   end desc,
              case          when @o=7 then id,(c*d)  end,
              case          when @o=8 then id,(c*d)  end desc
 

but i need id, a*b
not only id

The query doesn't compile of course, since you are selecting from "table" and are mixing GROUP BY ID with a*b, c*d (non-aggregates), but you get the idea with the order by clause

order by
case when @o in (5,6,7,8) then id end,
case when @o=1 then result1 end,
case when @o=2 then (a*b) end desc,
case when @o=3 then (c*d) end,
case when @o=4 then (c*d)end desc

If it is 5-8, it will sort first by the id column. Otherwise, that particular "sort column" ends up with NULLS for every record, meaning it has no effect, and moves on to the next sort condition.

Just to be clear, "result1" doesn't work either per your earlier question, since it has only been defined in the SELECT clause

order by
case when @o in (5,6,7,8) then id end,
case when @o=1 then (a*b) end,
case when @o=2 then (a*b) end desc,
case when @o=3 then (c*d) end,
case when @o=4 then (c*d)end desc

sorry i wrote wrong not column ID
this field repeats for example column ''CAR''

CASE statements return one value, so you cannot make it

CASE WHEN <condition> then COL1, COL2 ... ??  Only one please.

So you need another way to do it, as I have shown.  Just replace the word ID with CAR then.

order by
case when @o in (5,6,7,8) then car end,
case when @o=1 then (a*b) end,
case when @o=2 then (a*b) end desc,
case when @o=3 then (c*d) end,
case when @o=4 then (c*d)end desc

only one? ahhhh

then i  don't need 5,6,7,8 it will be good only 5

THX