Sqlite LIMIT / OFFSET query doubt

Question

Hello, I have simple question with Sqlite. What is the difference between this:

Select * from Animals LIMIT 100 OFFSET 50

and

Select * from Animals LIMIT 100,50

thanks in advance

Answer 1

The two syntax forms are a little confusing because they reverse the numbers:

LIMIT <skip>, <count>

Is equivalent to:

LIMIT <count> OFFSET <skip>

It's compatible with the syntax from MySQL and PostgreSQL. MySQL supports both syntax forms, and its docs claim that the second syntax with OFFSET was meant to provide compatibility with PostgreSQL. PostgreSQL docs show it only supports the second syntax.

By the way, using LIMIT without first using ORDER BY may not always give you the results you intend. In practice, SQLite will return the rows in some order, probably determined by how they're physically stored in the file. But this doesn't necessarily mean it's in the order you want. The only way to get a predictable order is to use ORDER BY explicitly.

Answer 2

The latter is an alternative syntax with one caveat:

If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradition is intentional - it maximizes compatibility with legacy SQL database systems.