Menu
I'm using the following code to get a list of notifications (total rows are 21):
List<Notification> list = new ArrayList<Notification>();
Cursor c = _db.query(TABLE_NAME, COL_ALL, null, null, null, null, order, get_limitStr(offset));
if(c != null && c.moveToFirst())
{
while(!c.isAfterLast())
{
Notification model = cursorToModel(c);
if(model != null)
{
list.add(model);
}
c.moveToNext();
}
c.close();
}
and the generated query for offset = 0 is
SELECT Id, Token, Title, Read, Message, Image, CreateDate, CreateDateFA FROM Notifications ORDER BY CreateDate DESC LIMIT 20,0
and it works as it's supposed to and returns 20 rows, when I increase offset by 1 (offset = 1) it returns only 1 row which is correct but the problem is when the offset is bigger than 1 then the query will be
SELECT Id, Token, Title, Read, Message, Image, CreateDate, CreateDateFA FROM Notifications ORDER BY CreateDate DESC LIMIT 20,2
and I thought it supposed to skip 20 * 2 rows and then starts taking rows from there, which either my thought or my query is wrong. What am I doing wrong here? Thanks
651
SQLite Limit:In the LIMIT clause, you can select a specific number of rows starting from a specific position using the OFFSET clause. For example, “LIMIT 4 OFFSET 4” will ignore the first 4 rows, and returned 4 rows starting from the fifth rows, so you will get rows 5,6,7, and 8.
Typically to get a specific row you can always request them by rowid , e.g. SELECT name FROM UnknownTable WHERE rowid = 1; However, there are some atypical situations that preclude this. You'll really want to read up on rowids to ensure that your table is going to behave as you want.
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.
SQLite SELECT statement is used to fetch the data from a SQLite database table which returns data in the form of a result table. These result tables are also called result sets.
LIMIT 20,2and I thought it supposed to skip 20 * 2 rows and then starts taking rows from there, which either my thought or my query is wrong.
LIMIT 20,2 skips first 20 rows and returns at most 2 remaining rows. It's the same as LIMIT 2 OFFSET 20.
Even the documentation says it's counter-intuitive:
Instead of a separate OFFSET clause, the LIMIT clause may specify two scalar expressions separated by a comma. In this case, the first expression is used as the OFFSET expression and the second as the LIMIT expression. This is counter-intuitive, as when using the OFFSET clause the second of the two expressions is the OFFSET and the first the LIMIT. This reversal of the offset and limit is intentional - it maximizes compatibility with other SQL database systems. However, to avoid confusion, programmers are strongly encouraged to use the form of the LIMIT clause that uses the "OFFSET" keyword and avoid using a LIMIT clause with a comma-separated offset.
If you want to achieve sort of result paging with page size of 20, use something like OFFSET k*20 LIMIT 20 where k is your zero-based page number.
84
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
