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

@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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

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

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.

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

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

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

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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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