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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hrvica5Author Commented:
THX
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.