LIMIT and OFFSET is not working properly in android

Question

My Database table:

Column1     Column2     Column3
First Col   Second Col  Third Col
First Row   First Row   First Row
Second Row  Second Row  Second Row
Third Row   Third Row   Third Row
Four Row    Four Row    Four Row
Fifth Row   Fifth Row   Fifth Row

I am trying below queries:

First Query:

String q = "select * from " + tableName + " LIMIT 2 OFFSET 3 ";
 sqlDB.rawQuery(q,null);

Second Query:

sqlDB.query(q, null,null, null, null, null, null, "3,2");

Both the queries giving me below result:

First Col   Second Col  Third Col
First Col   Second Col  Third Col

Instead of below result:

Third Row   Third Row   Third Row
Four Row    Four Row    Four Row

Am i missing something or doing wrong? to get this result.

Answer 1

The query

SELECT *
FROM tableName
LIMIT 2 OFFSET 3

means return two records, starting in the fourth position.

Your API call to SQLite's Android DB query

sqlDB.query(q, null,null, null, null, null, null, "3,2");

has a limit string of "3,2", which is in the format

"offset, limit"

In other words, the offset and limit switch positions, and therefore the queries are saying the same thing, and hence you get the same result sets.

FYI, I found nothing in the documentation about this, but I did find this SO question which mentions the syntax for the limit string in SQLite's Android API.

Update:

In order to get consistent behavior while using LIMIT and OFFSET, either in a native query or using the Android API, it probably makes sense to also use ORDER BY. Using an ordering on the result set allows an offset or a limit to make sense with regard to your underlying data. Database tables themselves are modeled after unordered sets. So using offset without ORDER BY most likely isn't what you want to be doing.