Dynamic order by case expression problem

Hello,
I want to do a dynamic order but with several criterias, my code look like:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price,stock
WHEN @.order = 'S' THEN stock,price
ELSE name,price
END
But it does not work, MSSQL doesn't like to have more than one value for
the order by, the code below works but that not what i want:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END
How can i do ?
ThanksOne method is with multiple CASE expressions in your ORDER BY clause:
SELECT name,price,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Hi
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
IF @.order="S"
SELECT name,price,stock FROM products ORDER BY stock,price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Untested:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
CASE WHEN @.order = 'P' THEN stock
WHEN @.order = 'S' THEN price
ELSE price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Uri's suggestion might be much better for performance|||Good one Uri and might perform better than the multiple Case.
You just forgot one:
IF @.order not in('S', 'P')
SELECT name,price,stock FROM products ORDER BY name,price
Or he can use Else.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ubd7mXHtFHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> IF @.order="S"
> SELECT name,price,stock FROM products ORDER BY stock,price
>
> "Not4u" <Not4u@.chez.com> wrote in message
> news:43203431$0$11421$626a14ce@.news.free.fr...
>|||Thanks it's work great.
Not4u wrote:
> Hello,
> I want to do a dynamic order but with several criterias, my code look like
:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||AK wrote:
> Uri's suggestion might be much better for performance
>
Before discovering the dynamic order by (with case), i used the "IF then"
My select statment is much more complicated than the exemple in this
post and i have multiple order by conditions, the code managing is
easier with the "ORDER BY CASE".
What do you mean by much better performance ?
Thanks|||if at compile time there is an appropriate index, then SQL Server can
satisfy one ORDER BY clause without a sort. If you are specific:
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
the optimizer has a better chance to give you a better plan FOR THIS
PARTICULAR BRANCH of your IF statement.
If you are not specific:
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
the optimizer will utilize "one size fits all" approach, it will always
sort. SQL Server is very good at sorting, but still sorting is not
repeat not free...|||AK wrote:
> if at compile time there is an appropriate index, then SQL Server can
> satisfy one ORDER BY clause without a sort. If you are specific:
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> the optimizer has a better chance to give you a better plan FOR THIS
> PARTICULAR BRANCH of your IF statement.
> If you are not specific:
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END,
> the optimizer will utilize "one size fits all" approach, it will always
> sort. SQL Server is very good at sorting, but still sorting is not
> repeat not free...
>
My request is like this :
SELECT name,
(select min(price) from Price
INNER JOIN Reference ON
Price.id_reference = Reference.id_reference
WHERE Reference.id_product = Products.id_product
) as 'price'
,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
ENDsql