sql 2008 order by

@o

Select id, (a*b) as result1, (c*d) as result2 from table
group by id
order by case  when @o=1 then result1
              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
 
how to order by in stored procedure?
when i do this i got exception

thx
hrvica5Asked:
Who is Participating?
 
cyberkiwiCommented:
No you need 5-8..., sorry I see where I went wrong

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

So let's say it is 6.  It will end up with

order by
case when @o in (5,6,7,8) then car end,    <<< ends up with car value here
case when @o in(1,5) then (a*b) end,    <<< ends up NULL
case when @o in (2,6) then (a*b) end desc,    <<< ends up (a*b) value here
case when @o in (3,7) then (c*d) end,    <<< ends up NULL
case when @o in (4,8) then (c*d)end desc    <<< ends up NULL


So in effect, you ended up with order by car, (a*b), which is what @o=6 is intended to achieve
0
 
cyberkiwiCommented:
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
0
 
mayank_joshiCommented:
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

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Lee SavidgeCommented:
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
 
0
 
hrvica5Author Commented:
but i need id, a*b
not only id
0
 
cyberkiwiCommented:
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.
0
 
cyberkiwiCommented:
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
0
 
hrvica5Author Commented:
sorry i wrote wrong not column ID
this field repeats for example column ''CAR''
0
 
cyberkiwiCommented:
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
0
 
hrvica5Author Commented:
only one? ahhhh

then i  don't need 5,6,7,8 it will be good only 5
0
 
hrvica5Author Commented:
THX
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.