artist artist - 1 year ago 116
Android Question

Query last N rows based on ID in SQLite

I have a SQLite database in which I am trying to read the last 8 rows in database using ID.

I am having trouble with the query. Can't seem to get it right.

public String lastEightRows(){
String[] columns = new String[] {KEY_ROWID, KEY_COLORS};
String result30 = " ";

String where = "ORDER BY" + KEY_ROWID + "DESC LIMIT" + 8;
//This is where I am not able to get the query right

Cursor c = ourDatabase.query(DATABASE_TABLE, columns, where, null, null, null, null);

int iRow = c.getColumnIndex(KEY_ROWID);
int iColors = c.getColumnIndex(KEY_COLORS);

for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
result30 = result30 + c.getString(iRow) + " " + c.getString(iColors) + " " + "\n";

return result30;

I got this error message

03-26 22:58:27.420: E/AndroidRuntime(1368): android.database.sqlite.SQLiteException: near "DESC": syntax error (code 1): , while compiling: SELECT _id, persons_colors FROM personsTable WHERE _id DESC LIMIT 8

Answer Source

From Android SQLiteDatabase API:

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

Query the given table, returning a Cursor over the result set.

Try this code instead

Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, KEY_ROWID + " DESC", "8");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download