ORDERing two UNION queries: Non-Subquery UNION Execution

http://dev.mysql.com/doc/internals/en/select-union.html

Does anyone know what this means? Can it be implemented in the flow of
php code without explicitly creating temp tables in the database?

My query is like:

$query = "
(SELECT
Table.field, [other fields]
FROM Table [other JOINs]
ORDER BY Table.field
)
UNION
(SELECT
Table2.field, [other fields]
FROM Table2 [other JOINs]
ORDER BY Table2.field
)
";

and I want the ORDER to apply to the whole UNIONed result.

John

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: ORDERing two UNION queries

At 03:04 14/04/2007, you wrote:

>Message-ID: <461FB44B.3010302@btinternet.com>
>Date: Fri, 13 Apr 2007 17:48:11 +0100
>From: ioannes
>Reply-To: ioannes@btinternet.com
>MIME-Version: 1.0
>To: php-db@lists.php.net
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>Content-Transfer-Encoding: 7bit
>Subject: ORDERing two UNION queries: Non-Subquery UNION Execution
>
>http://dev.mysql.com/doc/internals/en/select-union.html


You meant to refer to this page :
http://dev.mysql.com/doc/refman/5.1/en/union.html


>Does anyone know what this means? Can it be implemented in the flow
>of php code without explicitly creating temp tables in the database?
>
>My query is like:
>
>$query = "
> (SELECT
> Table.field, [other fields]
> FROM Table [other JOINs]
> ORDER BY Table.field
> )
> UNION
> (SELECT
> Table2.field, [other fields]
> FROM Table2 [other JOINs]
> ORDER BY Table2.field
> )
> ";
>
>and I want the ORDER to apply to the whole UNIONed result.


So, after reading the correct documentation, you'll now know to write
the query as

(SELECT
Table.field, [other fields]
FROM Table [other JOINs]
)
UNION
(SELECT
Table2.field, [other fields]
FROM Table2 [other JOINs]
)
ORDER BY field


Cheers - Neil

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php