|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic order by case expression problemI 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 One 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 -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Not4u" <No***@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 Show quote "Not4u" <No***@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 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:
Show quote > if at compile time there is an appropriate index, then SQL Server can My request is like this :> 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... > 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 END well I said *might* be better, not will be better.
I think what I said is not relevant to your situation 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. Show quote "Uri Dimant" <u***@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" <No***@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 Show quote "Not4u" <No***@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 Thanks it's work great.
Not4u wrote: Show quote > 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  |
|||||||||||||||||||||||
Other interesting topics