CODEWITHSUNDEEP
mysqlsql
user5175034
user5175034

Reputation:

MySQL MIN() and MAX() with LIMIT

I'm trying to fetch a MIN() and MAX() value from a query so that I can use the resulting values in a PHP function but can't seem to work out how to do it because there is a LIMIT involved.

SELECT MIN(ID) AS MinID, MAX(ID) AS MaxID 
FROM parts_listing 
WHERE BaseGroup = 0
LIMIT 0,50;

This is a dynamically-generated LIMIT used by a pagination function so in this example, should give MinID = 1 and MAXID = 50 but instead gives MinID = 1 and MinID = 129, which is the number of total records in BaseGroup 0. If I use any of the other BaseGroup values, it also gives the total values. If I change the starting record of LIMIT to, for example, LIMIT 10,50 I get nothing whatsoever.

I realized that there are similar questions here but they did not help in this specific case. Any ideas?

Upvotes: 2

Views: 9898

Answers (2)

dnoeth
dnoeth

Reputation: 60482

LIMIT is applied after processing the MIN/MAX, when there's only a single row left.

You need to move it to a Derived Table: 

SELECT MIN(ID) AS MinID, MAX(ID) AS MaxID 
FROM
 (
   SELECT ID 
   FROM parts_listing 
   WHERE BaseGroup = 0
   LIMIT 0,50
 ) as dt

And you probably need an ORDER BY, too.

Upvotes: 7

Ing. Gerardo Sánchez
Ing. Gerardo Sánchez

Reputation: 1667

LIMIT does not limit how record are using mysql to calculate MIN or MAX functions. ALL RECORDS that matches WHERE criteria are used to calculate results

In other word LIMIT has no any sense with aggregate functions

LIMIT does not see query, just see result set, if query outputs more than one row, limits works with that

You need to use WHERE clausule, to "limit" rows used in aggregate functions

Upvotes: 2

Related Questions