CODEWITHSUNDEEP
mysqllimit
Thiago M.
Thiago M.

Reputation: 33

Mysql LIMIT not limiting my query

Why the following query return records from 32 - 54 instead of its specified in LIMIT clause? 

SELECT
    info.*,
    type.titulo AS type_name,
    category.titulo AS category_name
FROM infos_infos info
LEFT JOIN infos_categories category
    ON category.id=info.category
LEFT JOIN infos_canais type
   ON type.id=info.type
WHERE
    info.active=1 AND
    type.active=1
GROUP BY info.id
LIMIT 32,48

Upvotes: 0

Views: 805

Answers (3)

Faridul Khan
Faridul Khan

Reputation: 2007

SQL Should be:

SELECT
        info.*,
        type.titulo AS type_name,
        category.titulo AS category_name
    FROM infos_infos info
    LEFT JOIN infos_categories category
        ON category.id=info.category
    LEFT JOIN infos_canais type
       ON type.id=info.type
    WHERE
        info.active=1 AND
        type.active=1
    GROUP BY info.id
    LIMIT 32,16

Upvotes: 0

caramba
caramba

Reputation: 22480

From the docs it says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

So what you want it so set LIMIT like

LIMIT 32, 16 # this will return rows from 32 - 48

To answer your question "Why the following query return records from 32 - 54?" It's because you set start at 32 and you do not have more then 54 items. That is why. The LIMIT 32,48 will try to show rows from 32 - 80!

Upvotes: 4

Patrick Artner
Patrick Artner

Reputation: 51633

https://dev.mysql.com/doc/refman/5.7/en/select.html

LIMIT as you use it is a OFFSET + Count , you probably have less than 80 (32+48) result, so it shows you from offset 32 till end of your data

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

Upvotes: 2

Related Questions