"Lorderon" <[EMAIL PROTECTED]> wrote:
> Since I don't use MySQL 4.1, is this query OK?
>
> SELECT SQL_CALC_FOUND_ROWS * FROM
> ((SELECT * FROM t1 WHERE id>100 GROUP BY country)
> UNION ALL
> (SELECT * FROM t2 WHERE id>150 GROUP BY country)
> LIMIT 0,10);
>
> I want this to return all the rows of the UNION sub-query there.. the reason
> I used it as sub-query is that I want to count the rows, and then use:
> SELECT FOUND_ROWS();
> to get the number of rows supposed to be retuned without using the LIMIT..
>
> Is all above correct?
First of all your subqueries are illegal, because you select columns that are not in
the GROUP BY clause.
Since 4.1 you can write:
SELECT SQL_CALC_FOUND_ROWS * FROM
( SELECT country FROM t1 WHERE id>100 GROUP BY country
UNION ALL
SELECT country FROM t2 WHERE id>150 GROUP BY country)
as union_table
LIMIT 0,10;
But the following query gives you the same result:
(SELECT SQL_CALC_FOUND_ROWS country FROM t1 WHERE id>100 GROUP BY country)
UNION ALL
(SELECT country FROM t2 WHERE id>5 GROUP BY country) LIMIT 0,10;
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
