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

@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

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

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

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.

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