mysql limit as subquery

Question

can anyone help me to do something similar to this i.e., use subquery to set the limit :

select * from table limit (select count (*) as max_limit from table);

Any help would be highly appreciated. thanks.

thanks, well, i have a table which has close to 30000+ records and counting and everytime I query, it limits to initial 1000 or so.. I kept setting the limits based on guess but thought to find some permanent solution so that i don't have to bother even if the count goes beyond 100K and the query shouldn't truncate the results. — Iqlas Uddin, Jul 31 '14 at 09:01
I've never heard of a query limiting spontaneously. If you don't have a LIMIT clause it returns ALL the results. — Barmar, Jul 31 '14 at 09:03
Maybe the limit is being imposed by whatever UI you're using to view the results. Are you using phpMyAdmin? See http://stackoverflow.com/questions/1413288/phpmyadmin-change-default-number-of-rows-displayed — Barmar, Jul 31 '14 at 09:06
@Barmar I'm using mysql workbench and yes I realised the limit is imposed by UI. but we use workbench for most of the reports and hence your reply was helpful. cheers — Iqlas Uddin, Jul 31 '14 at 09:17
Why didn't you just ask how to remove that limit? I found the above answer in 10 seconds by googling "mysql workbench limit 1000" — Barmar, Jul 31 '14 at 09:21

score 2 · Accepted Answer · answered Jul 31 '14 at 09:01

2

You need to use dynamic SQL because the LIMIT parameters have to be literals.

SET @SQL = CONCAT('select * from table limit ',
                  (select count (*) as max_limit from table));
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Jul 31 '14 at 09:01

Barmar

741,623
53
500
612

your answer was what i precisely wanted because we use queries over a variety of UI's and removing those limits individually in all the UI's is much harder than few lines of dynamic code which can be used on cross-UI. thanks for the answer tho. but if you want remove it, i will do it. – Iqlas Uddin Jul 31 '14 at 09:29
Since MySQL Workbench is the only UI that has this limit, why do you need a cross-UI solution? – Barmar Jul 31 '14 at 09:30
E.g. phpMyAdmin defaults to showing only 30 rows, but I don't think adding a `LIMIT` clause higher than 30 will override that. – Barmar Jul 31 '14 at 09:31

mysql limit as subquery

1 Answers1