Sorting
in [Programming SQL]
|
From: simon on 15 Apr 2010 06:28 Hi, is it possible to use variable for sorting? Something like: declare @sort varchar(10) SET @sort='column1 DESC' SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table Is dynamic SQL only option? Thank you, Simon
From: Dooza on 15 Apr 2010 06:48 On 15/04/2010 11:28, simon wrote: > Hi, > > is it possible to use variable for sorting? > > Something like: > > declare @sort varchar(10) > SET @sort='column1 DESC' > > SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table > > Is dynamic SQL only option? You can use CASE to help you order dynamically. Here is one I use: ORDER BY CASE @sort WHEN 1 THEN sdl_item END ASC, CASE @sort WHEN 3 THEN sor_orderdate END ASC, CASE @sort WHEN 5 THEN sdl_despatch END ASC, CASE @sort WHEN 2 THEN sdl_item END DESC, CASE @sort WHEN 4 THEN sor_orderdate END DESC, CASE @sort WHEN 6 THEN sdl_despatch END DESC Its used on a webpage with a drop down box, the value determines the type of sort and its direction. If all the sort columns were of the same type you can cut it down to just 2 CASE's, one for ASC and one for DESC, but if you have mixed types, like datetime and varchar then you need one CASE per sort type. Dooza
From: Uri Dimant on 15 Apr 2010 07:19 Simon No, use CASE expression ORDER BY CASE WHEN @sort = 'col1' AND @dir = 1 THEN col1 END ASC, CASE WHEN @sort = 'col1' AND @dir = 0 THEN col1 END DESC, CASE WHEN @sort = 'col2' AND @dir = 1 THEN col2 END ASC, CASE WHEN @sort = 'col2' AND @dir = 0 THEN col2 END DESC "simon" <zupan.net(a)gmail.com> wrote in message news:a0eb3a56-f273-461a-b941-59be598d19f9(a)12g2000yqi.googlegroups.com... > Hi, > > is it possible to use variable for sorting? > > Something like: > > declare @sort varchar(10) > SET @sort='column1 DESC' > > SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table > > Is dynamic SQL only option? > > Thank you, > Simon
From: simon on 15 Apr 2010 07:27 On 15 apr., 13:19, "Uri Dimant" <u...(a)iscar.co.il> wrote: > Simon > No, use CASE expression > ORDER BY CASE WHEN @sort = 'col1' AND @dir = 1 THEN col1 END ASC, > CASE WHEN @sort = 'col1' AND @dir = 0 THEN col1 END DESC, > CASE WHEN @sort = 'col2' AND @dir = 1 THEN col2 END ASC, > CASE WHEN @sort = 'col2' AND @dir = 0 THEN col2 END DESC > > "simon" <zupan....(a)gmail.com> wrote in message > > news:a0eb3a56-f273-461a-b941-59be598d19f9(a)12g2000yqi.googlegroups.com... > > > > > Hi, > > > is it possible to use variable for sorting? > > > Something like: > > > declare @sort varchar(10) > > SET @sort='column1 DESC' > > > SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table > > > Is dynamic SQL only option? > > > Thank you, > > Simon- Skrij navedeno besedilo - > > - Prika¾i citirano besedilo - As I thought. Order by case is usually much slower than create dynamic sql. So, I'll stay with dynamic sql. thanks, S
From: Uri Dimant on 15 Apr 2010 07:39
simon > Order by case is usually much slower than create dynamic >sql. Hmm, can you provide me with your test script?? "simon" <zupan.net(a)gmail.com> wrote in message news:f31833f6-cf4a-4f5e-8c79-796d899a661e(a)u34g2000yqu.googlegroups.com... On 15 apr., 13:19, "Uri Dimant" <u...(a)iscar.co.il> wrote: > Simon > No, use CASE expression > ORDER BY CASE WHEN @sort = 'col1' AND @dir = 1 THEN col1 END ASC, > CASE WHEN @sort = 'col1' AND @dir = 0 THEN col1 END DESC, > CASE WHEN @sort = 'col2' AND @dir = 1 THEN col2 END ASC, > CASE WHEN @sort = 'col2' AND @dir = 0 THEN col2 END DESC > > "simon" <zupan....(a)gmail.com> wrote in message > > news:a0eb3a56-f273-461a-b941-59be598d19f9(a)12g2000yqi.googlegroups.com... > > > > > Hi, > > > is it possible to use variable for sorting? > > > Something like: > > > declare @sort varchar(10) > > SET @sort='column1 DESC' > > > SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table > > > Is dynamic SQL only option? > > > Thank you, > > Simon- Skrij navedeno besedilo - > > - Prika�i citirano besedilo - As I thought. Order by case is usually much slower than create dynamic sql. So, I'll stay with dynamic sql. thanks, S |