Sqlite Get x rows, then next x rows

Question

Im developing android app, which use SQLite database.

I have ListView which uses data from Database to show a list (see picture).

The picture is decoded in Base64 String and stored in Database. My problem is this Log:

10-19 16:51:36.612: W/CursorWindow(15151): Window is full: 
requested allocation 136877 bytes, free space 78836 bytes, window size 2097152 bytes

It skypes a lot of Frames, because the read time grows. This is because i read always x+10 rows. 1st, it reads 10 rows, then 20, then 30 and go on...

The solution, what i want to use is, get rows from 0-10, 11 - 20, 21 - 30 and so on. How to achieve this? I just need the Sql query.

EDIT: my query

String columns[] = {KEY_ID, KEY_NAME, KEY_RATING, KEY_CUISINE, KEY_IMAGE};
Cursor cursor = db.query(TABLE_RECIPES, columns, null, null, null, null, KEY_NAME, lim);

Answer 1

Use the rawQuery method, and specify the limit keyword.

e.g.:

"SELECT * FROM myTable limit 10" <-- get the 1st 10 rows
"SELECT * FROM myTable limit 10, 20" <-- get the 2nd 10 rows between 10 and 20, etc

This should get you started.

Answer 2

I tried the answer above and the result was:

"SELECT * FROM myTable limit 10" <-- Got the first 10 rows
"SELECT * FROM myTable limit 10, 20" <-- Got the first 20 rows !

So the answer above did not work for me, what worked for me was this:

This is my table:

To get the first 10:

select * from quran_text where sura = 2 limit 0, 10

Second 10:

select * from quran_text where sura = 2 limit 10, 10

Third 10:

select * from quran_text where sura = 2 limit 20, 10

in the picture it shows only 5 record cuz of space, but it gets the right result.

so to make it as pattern:

to get it for first 10 here X = 1;

creating query in java:

String query = "select * from quran_text where sura = 2 limit "+((X-1)*10)+", 10";

Answer 3

Beware of the syntax:

LIMIT row_count OFFSET offset;

versus

LIMIT offset, row_count;

Order of arguments is not the same