Search code examples
mysqlsqlsql-limit

SQL limit gives bad results

I have a problem with using SQL LIMIT statement. I have 21 records stored in database and I want to get results piecewise. I have written this query: 

SELECT * FROM table JOIN another_table ON XXX = YYY WHERE XXX = ? ORDER BY col DESC LIMIT ?, ?

Here is num of rows which I get when I use this values:

LIMIT:  0 .. 10  --->  num of rows 10     -> Correct
LIMIT: 10 .. 20  --->  num of rows 11     -> Incorrect
LIMIT: 20 .. 30  --->  num of rows 0      -> Incorrect

Code is used in php function and for database operations I use PDOStatement class but this error is also manifested in the use of the MySQLi class.

Does anyone know where the problem is?

Solution

  • When you say:

    LIMIT X, Y

    You are saying X is the offset and Y is the count. What you want is:

    LIMIT 0, 10
LIMIT 10, 10
LIMIT 20, 10

    or, use the OFFSET keyword and use:

    LIMIT 10 OFFSET 0
LIMIT 10 OFFSET 10
LIMIT 10 OFFSET 20