I need to get the row count for individual SELECT statements in an UNION ALL SELECT query that uses LIMIT. The MySQL docs are pretty clear about getting the global row count for the query (place individual SELECTs in parenthesis and place an SQL_CALC_FOUND_ROWS only in the first statement, then get FOUND_ROWS() the usual way). However, I also need the result row count for the individual SELECT statements. The query, simplified:
(SELECT SQL_CALC_FOUND_ROWS `field1`,`field2` FROM `db`.`table1` WHERE `id`>1000)
UNION ALL
(SELECT `field1`,`field2` FROM `db`.`table2` WHERE `id`>1000)
UNION ALL
...
(SELECT `field1`,`field2` FROM `db`.`tableN` WHERE `id`>1000)
LIMIT 0,10
If SQL_CALC_FOUND_ROWS is placed in every SELECT statement an "Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'" error is issued.
Google this a lot, read related messages here, to no avail. Might be something really simple, I just can't get my mind around it.
Best Answer
You're getting that error because SQL_CALC_FOUND_ROWS can be used on a query to return a single number into FOUND_ROWS().
You can get back counts doing this:
if that helps.
If you want to return the rows from the tables with a count then modify it:
From your question, it's not precisely clearly what you're trying to achieve.