Limiting a SQLite query in android

Question

I am using an SQLite database in my android application, and I have a function which selects the rows from a certain table:

public Cursor getAllDiscounts() {
    // return db.query(table, columns, selection, selectionArgs, groupBy,
    // having, orderBy);
    return db.query(DATABASE_TABLE, new String[] { KEY_ROWID,
            KEY_PORTALNAME, KEY_TITLE, KEY_TITLESHORT, KEY_DEALURL,
            KEY_ENDDATE, KEY_COORDS, KEY_CITY, KEY_IMAGEDEAL,
            KEY_CLICKPRICE, KEY_CONVERSIONPERCENTAGE, KEY_FINALPRICE,
            KEY_ORIGINALPRICE, KEY_SALES, KEY_KATEGORIJA, KEY_POPUST },
            null, null, null, null, null, null);
}

What I want to do, is to select rows starting at a certain row and limit the result to another number. So, for instance, I want to start at the tenth row and select the following 20 rows. I tried it like this:

public Cursor getAllDiscounts() {
    return db.query(DATABASE_TABLE, new String[] { KEY_ROWID,
            KEY_PORTALNAME, KEY_TITLE, KEY_TITLESHORT, KEY_DEALURL,
            KEY_ENDDATE, KEY_COORDS, KEY_CITY, KEY_IMAGEDEAL,
            KEY_CLICKPRICE, KEY_CONVERSIONPERCENTAGE, KEY_FINALPRICE,
            KEY_ORIGINALPRICE, KEY_SALES, KEY_KATEGORIJA, KEY_POPUST },
            null, null, null, null, null, "10, 20");
}

but the application crashes. I also tried with "LIMIT 10,20" instead of "10, 20", but that doesn't work either. Anyone?

Duplicate of http://stackoverflow.com/questions/6361668/how-to-use-the-limit-argument-in-an-sqlite-query-with-android, perhaps? — hd1, Dec 03 '12 at 19:40

score 9 · Answer 1 · answered Feb 27 '14 at 12:02

the limit clause should be "10,20" with no space between the coma and the 20.

public Cursor getAllDiscounts() {
    return db.query(DATABASE_TABLE, new String[] { KEY_ROWID,
            KEY_PORTALNAME, KEY_TITLE, KEY_TITLESHORT, KEY_DEALURL,
            KEY_ENDDATE, KEY_COORDS, KEY_CITY, KEY_IMAGEDEAL,
            KEY_CLICKPRICE, KEY_CONVERSIONPERCENTAGE, KEY_FINALPRICE,
            KEY_ORIGINALPRICE, KEY_SALES, KEY_KATEGORIJA, KEY_POPUST },
            null, null, null, null, null, "10,20");
}

NOTE: The limit string is of the form: ",". So using the original string " limit 10 offset 20" would result in "20,10" — tamsler, Mar 27 '14 at 18:36

score 4 · Answer 2 · answered Dec 03 '12 at 19:46

Use "limit 10 offset 20" as limit clause.

    public Cursor getAllDiscounts() {
    return db.query(DATABASE_TABLE, new String[] { KEY_ROWID,
            KEY_PORTALNAME, KEY_TITLE, KEY_TITLESHORT, KEY_DEALURL,
            KEY_ENDDATE, KEY_COORDS, KEY_CITY, KEY_IMAGEDEAL,
            KEY_CLICKPRICE, KEY_CONVERSIONPERCENTAGE, KEY_FINALPRICE,
            KEY_ORIGINALPRICE, KEY_SALES, KEY_KATEGORIJA, KEY_POPUST },
            null, null, null, null, null, " limit 10 offset 20");
}

score 4 · Answer 3 · answered Dec 03 '12 at 19:53

4

Try this:

db.rawQuery("SELECT * FROM " + TABLE_NAME + " ORDER BY " + ORDER_BY + " LIMIT 0, 20", NULL);

answered Dec 03 '12 at 19:53

Ascension

2,599
2
15
13

score 0 · Answer 4 · answered Feb 23 '14 at 17:03

0

db.rawQuery("SELECT * FROM com_kkorm_data_Entity WHERE id!=0 ORDER BY id LIMIT 0,2",null);

Maybe my sql can help you understand LIMITsql

answered Feb 23 '14 at 17:03

kkmike999

101
2
5

Limiting a SQLite query in android

4 Answers4

Linked