Page 1 of 1

Row count using limit

Posted: Tue Jul 12, 2005 11:30 am
by icarpenter
Hi I am running a query using the limit function

Code: Select all

SELECT * FROM table limit 40,5
But I would also like to return a row count of the select query without the limit function...

Does anyone know a way of doing this as I want to avoid running the query again without using limit to get a row count if I can help it...

Rgds Ian

Posted: Tue Jul 12, 2005 11:50 am
by John Cartwright
The only way that I can see is a subquery,

Code: Select all

SELECT *, COUNT(SELECT * FROM `table`) AS `total` LIMIT 40,5
Of course this is only supported by mysql 4.1 and greater

Posted: Wed Jul 13, 2005 2:59 am
by icarpenter
Thanks Jcart...

I tried this on version 5.0.0 but I recieved an SQL syntax error, do you have any idea what I might be doing wrong?

Code: Select all

SELECT *, COUNT(SELECT * FROM `my_table`) AS `total` LIMIT 40,5
Thanks Ian

Posted: Wed Jul 13, 2005 4:45 am
by phpScott
what was the error?

Posted: Wed Jul 13, 2005 6:49 am
by icarpenter
phpscott this was the error!

Code: Select all

[localhost] ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM `my_table`) AS `total` LIMIT 40,5' at line 1
Rgds Ian