CODEWITHSUNDEEP
mysql
revo
revo

Reputation: 48701

Issue with using MAX()

I was answering a question on SO that encountered this issue.

Why I can't use MAX() within a LIMIT?

SELECT *
FROM table
ORDER BY id DESC
LIMIT 0, MAX(id)

Or

SELECT *, MAX(id) AS m
FROM table
ORDER BY id DESC
LIMIT 0, m

Both give a similar syntax 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 'MAX(id)' at line 4

Wanted query:

SELECT *
FROM table
ORDER BY id DESC
LIMIT 0, MAX(id)-5

Upvotes: 0

Views: 84

Answers (5)

rakeshjain
rakeshjain

Reputation: 1761

You can use the HAVING clause with MAX(id) to obtain your desired result

SELECT *
FROM table1
HAVING id<((SELECT Max(id) FROM table1)-5)
ORDER BY id DESC

Fiddle http://sqlfiddle.com/#!2/16e50/3

Upvotes: 1

Flavio Garcia
Flavio Garcia

Reputation: 1671

The function MAX cannot be used on the limit.

Here is the documentation:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max

The count argument in the limit should be a value so you cannot execute that in one step. Here is the select documentation:

http://dev.mysql.com/doc/refman/5.0/en/select.html

You can do is:

SET @a=(SELECT MAX(ID) FROM table);
PREPARE STMT FROM 'SELECT * FROM table ORDER BY id DESC LIMIT 0, ?';
EXECUTE STMT USING @a;

So it is a tree step here. Get max in a variable. Prepare a statement and than execute it applying the variable to the statement.

Upvotes: 1

cdhowie
cdhowie

Reputation: 168948

MAX() is an aggregate function over the result rows, but LIMIT is a clause that constrains the number of result rows. In short, you cannot use a function that depends on the result rows while you are still determining which rows will be in the result -- that simply doesn't make any sense.

According to the documentation, LIMIT arguments must either be integer constants or parameters of a prepared query (emphasis mine):

LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

The syntax specification simply does not allow a column or function to be used. You would have to pre-compute the value and then use it in a prepared query (or by string substitution, though I would avoid that).

Based on the query you gave in your question:

SELECT *
FROM table
ORDER BY id DESC
LIMIT 0, MAX(id)-5

I suspect that this is the query you actually want:

SELECT *
FROM table
WHERE id <= (SELECT MAX(id) FROM table) - 5
ORDER BY id DESC

Even if it were valid, the first query you gave will not do what you expect if there are gaps in the sequence of the id column (for example, if a row was deleted).

Upvotes: 1

John Woo
John Woo

Reputation: 263683

An alternative if you want to get your desired result is to create a dynamic sql.

SET @maxID = (SELECT MAX(ID) FROM tableName);
SET @sql = CONCAT('SELECT *
                   FROM tableName
                   ORDER BY ID DESC
                   LIMIT 0,', @maxID);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13455

ISSUE IS WIth your query::

Select 
* 
from
myTable,
(SELECT MAX(id) as n
FROM table) temp
ORDER BY id DESC
LIMIT 0, temp.n

Upvotes: -1

Related Questions