Limiting a SQLite query in android [closed]

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?

Irremeable

Attach the logcat of the error when it crashes please. – Shimkus

Duplicate of #6362168, perhaps? – Node

Answer 1

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");
}

Resurrection

NOTE: The limit string is of the form: "<offset>,<limit>". So using the original string " limit 10 offset 20" would result in "20,10" – Tricky

Answer 2

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");
}

Destinee

Answer 3

Try this:

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

Taite

Answer 4

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

Leon