Menu
If a limit count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows). LIMIT ALL is the same as omitting the LIMIT clause, as is LIMIT with a NULL argument. OFFSET says to skip that many rows before beginning to return rows.
Introduction to SQLite LIMIT clause You use the LIMIT clause to constrain the number of rows returned by the query. For example, a SELECT statement may return one million rows. However, if you just need the first 10 rows in the result set, you can add the LIMIT clause to the SELECT statement to retrieve 10 rows.
The SQL LIMIT clause constrains the number of rows returned by a SELECT statement. For Microsoft databases like SQL Server or MSAccess, you can use the SELECT TOP statement to limit your results, which is Microsoft's proprietary equivalent to the SELECT LIMIT statement.
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.
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.
I made some tests and there is no difference in performance.
That is only for compatability with other sql languages.
Running time of both versions is same.
I made sqlite db with table1 with 100000 rows. I run next test
long timeLimitOffset = 0;
long timeLimitComma = 0;
for (int i = 0; i < 100000; i++)
{
//first version
timeLimitOffset += SqlDuraction("Select * from table1 order by col1 LIMIT " + (i + 1) + " OFFSET " + (1001 - i) + "");
// second version
timeLimitComma += SqlDuraction("Select * from table1 order by col1 LIMIT " + (1001 - i) + " , " + (i + 1) + "");
}
Times vary for 0.001 of a second
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With