have a query with 4 selects in union and want to use count() and limit on the whole

[mikek12004]

with one query I would something like this

SELECT COUNT(id) AS numrows FROM pelates where owned='112';
 

to get the number of results and this

SELECT * FROM pelates where owned='122' LIMIT 10,20
 

to get the subset I need, but now I have a union of 4 selects and I want the count of the whole and the limit to be applied to the whole results also any ideas?

[SLauren]

You can use "SQL_CALC_FOUND_ROWS" to get the number of rows being resulted out of the query executed and after executing the query assign "FOUND_ROWS()" to your out parameter which will hold the number of records resulted. You can use the LIMIT keyword on the whole query.

For example:

SELECT SQL_CALC_FOUND_ROWS DISTINCT ID,Name FROM Table1 WHERE <Condition>
UNION
SELECT DISTINCT ID,Name FROM Table2 WHERE <Condition> LIMIT 1,50;
 
SET outParam := FOUND_ROWS();

Hope this could help.

Thanks,
Lauren