MySQL Is doing subquery after LIMIT syntax possible? If not, why?

Question

I have MySQL Server version 5.1.53. I was looking for an hour to answer this question by myself. Including read the documentation itself at http://dev.mysql.com/doc/refman/5.1/en/select.html

Currently, I run this query.

SELECT dv2.timestamp 
FROM data_val AS dv2
WHERE dv2.timestamp > '2011-06-10 22:26:25' ORDER BY dv3.timestamp DESC 
LIMIT 1

Then I was trying to eliminate the ORDER BY syntax by determining the calculation of MAX_QUERIES minus 1. By doing that I could write,

SELECT  (COUNT(*)-1) total 
FROM data_val AS dv2a
WHERE dv2a.timestamp > '2011-06-10 22:26:13'

Finally the query becomes,

SELECT dv2.timestamp 
FROM data_val AS dv2
WHERE dv2.timestamp > '2011-06-10 22:26:13' 
LIMIT (
     SELECT  (COUNT(*)-1) total 
     FROM data_val AS dv2a
     WHERE dv2a.timestamp > '2011-06-10 22:26:13'
     ), 1

And the error is:

#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 (COUNT(*)-1) total FROM data_val AS dv2a ' at line 4

I also tried to put the subquery after OFFSET syntax. but still error.

Do you have any idea why my sub-query doesn't work?

I need technical details with short, simple, and clean explanation.

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

From the MySQL manual: http://dev.mysql.com/doc/refman/5.5/en/select.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

The MySQL query optimizer needs to resolve the limit parameters to a constant before running the query, or it will not know how many rows to return.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 10 '11 at 22:41

Johan

74,508
24
191
319

In short, MySQL does not execute my sub query first? – rhzs Jun 10 '11 at 22:58
@exodream, Exactly, it will refuse to do so. Because that how it's programmed. – Johan Jun 11 '11 at 10:12

score 0 · Answer 2 · answered Jun 10 '11 at 22:23

0

You can't imbed a query result for a limit parameter

answered Jun 10 '11 at 22:23

Bohemian

412,405
93
575
722

1

why? I need technical details. – rhzs Jun 10 '11 at 22:27

MySQL Is doing subquery after LIMIT syntax possible? If not, why?

2 Answers2

Linked