Sqlite LIMIT / OFFSET query

sqlite limit
Pablo picture Pablo · Jul 24, 2010 · Viewed 94.6k times · Source

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

Answer

Bill Karwin picture Bill Karwin · Jul 24, 2010

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, and SQLite's docs show that it supports both, recommending the second syntax to avoid confusion.

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.

Related questions

How do you enable LIMIT for DELETE in SQLite?

Using PHP, I have a simple database that may store multiple items with the same content. I want to delete the first occurrence of an instance when I use DELETE. How do you enable LIMIT for DELETE in SQLite using …

Read More
Get data from database Android

I have a page which can retrieve user data from database but after whole day of trying, I am only able to get the table column name but not the value inside. this is my code to create database public …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More