Re: Dynamic ORDER BY

Tech-Archive recommends: Speed Up your PC by fixing your registry

A few comments:

First of all, I find it much easier (and more logical) to wrap UNIONs in a
derived table for ordering:

SELECT *
FROM
(
SELECT Col11, Col12, Col13 FROM Table1 WHERE ....

UNION

SELECT Col21, Col22, Col23 FROM Table2 WHERE ....
) AS x (Col1, Col2, Col3)
ORDER BY
x.Col1,
x.Col2 DESC,
x.Col3


Second, you can stack case expressions to selectively sort (although it may
get confusing)... I have done something like the following in a project that
required no dynamic SQL:

ORDER BY
CASE @SortOrder1
WHEN 'ASC' THEN
CASE @SortCol1
WHEN 1 THEN ColA
WHEN 2 THEN ColB
...
ELSE NULL
END
ELSE NULL
END ASC,
CASE @SortOrder1
WHEN 'DESC' THEN
CASE @SortCol
WHEN 1 THEN ColA
WHEN 2 THEN ColB
...
ELSE NULL
END
ELSE NULL
END DESC,
...

So what's happening here?

If @SortOrder1 is 'ASC', a column value will be used in the ASC sort (look
right after the END of the outer CASE expressions for the sort); otherwise,
it will sort NULL, which means that the next sort condition will be used to
break the ties (every row will be tied) -- the next sort condition is sorted
descending, based on @SortOrder1's value of 'DESC'. We did this for every
sort column, for ascending/descending. I hope this makes sense. If not,
write back and I'll try to explain it differently...

Note that I would much prefer dynamic SQL to this technique from both a
readability and performance standpoint. But if you can't use dynamic SQL
and do need dynamic sorting, this will work.


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--


"LUIS" <LUIS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:556D0DFB-D121-4826-9A04-B3BE8F7F21F6@xxxxxxxxxxxxxxxx
> Adam
>
> Thank you very much for your interest. Query is next:
>
>
> SELECT Col11, Col12, Col13 FROM Table1 WHERE ....
>
> UNION
>
> SELECT Col21, Col22, Col23 FROM Table2 WHERE ....
>
> ORDER BY ( 1 / 2,1 / 1 DESC, 3)
>
>
> Column Types for Col11/Col21,Col11/Col21,Col11/Col21 are the similar. For
> SELECTs UNIONed is necessary use numbers only. Last code line is to show
> what
> I want to do, to select betwwen 3 types of order criteria.
>
> Beforehand, thank you very much.
>
> --
> Luis Garcia
> IT Consultant
>
>
> "Adam Machanic" wrote:
>
>> I don't think it would be a problem with or without dynamic SQL ... but
>> can
>> you share some more information -- show exactly what you're trying to do?
>>
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>>
>> "LUIS" <LUIS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:FC98B0BE-41F5-4E44-8CBD-7D2DB80FC40D@xxxxxxxxxxxxxxxx
>> > Adam
>> >
>> > Thank you, very much for answering. Just one little thing. I want to
>> > use
>> > ORDER BY in a query with some SELECTs UNIONed; and some ORDER BY
>> > criteria
>> > have more than one column. I think you suggestion will not work. Is
>> > there
>> > another way, or only way to follow is Dynamic SQL?
>> >
>> > Once again, thank you very much.
>> >
>> > --
>> > Luis Garcia
>> > IT Consultant
>> >
>> >
>> > "Adam Machanic" wrote:
>> >
>> >> There are various ways to do that... one is to use a case expression:
>> >>
>> >> ORDER BY
>> >> CASE @MyOrderVariable
>> >> WHEN 1 THEN SomeCol
>> >> WHEN 2 THEN SomeOtherCol
>> >> WHEN 3 THEN ThirdCol
>> >> ....
>> >> ELSE NULL
>> >> END
>> >>
>> >>
>> >> Another is to use dynamic SQL. Depending on your environment one or
>> >> the
>> >> other may be more appropriate.
>> >>
>> >>
>> >> --
>> >> Adam Machanic
>> >> Pro SQL Server 2005, available now
>> >> www.apress.com/book/bookDisplay.html?bID=457
>> >> --
>> >>
>> >>
>> >> "LUIS" <LUIS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:87500F36-072A-46CD-BAB5-AE89AEEA7A58@xxxxxxxxxxxxxxxx
>> >> > Is there a way to make a Dynamic ORDER BY, allowing some kind of
>> >> > "variables"
>> >> > to to pass values and define it ?
>> >> >
>> >> > Beforehand, thank you very much.
>> >> > --
>> >> > Luis Garcia
>> >> > IT Consultant
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Dynamic ORDER BY
    ... from the CASE expression as numbers instead of ordinal positions (that's one ... you can stack case expressions to selectively sort (although it ... >> required no dynamic SQL: ... >> END ASC, ...
    (microsoft.public.sqlserver.datamining)
  • DYNAMIC SQL TECHNIQUE
    ... with dynamic SQL i'm of all sort of quoting issues and the ugliness of ... with quoting and inserting variables into dynamic sql, ... set @ratdog = REPLACE ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure with mulitple items passed in string
    ... stored procedure to use in a where clause. ... I would probably send this some sort of delimited sring. ... Would I have to use dynamic sql to handle this? ...
    (microsoft.public.sqlserver.programming)
  • Re: is this bad?
    ... > feelings about dynamic SQL. ... > Consider whether the front end can sort the results itself. ... >> I am using a stored procedure to retrieve data for an asp.net datagrid ...
    (microsoft.public.sqlserver.programming)
  • Re: What parts of a SQL query can be parameterized and what parts cant?
    ... > FROM Employees ... Specifies a column on which to sort. ... FROM (SELECT 'John' AS First_name ... UNION ALL SELECT 'George' ...
    (microsoft.public.sqlserver.programming)