barmi barmi - 3 months ago 15
Android Question

SQLite get second to last element

I have to get two last elements from table (or just id). My element's id is increased but not increment (next value is always larger but it is possible that more than 1 - elements are created by someone else and I am not supposed to modified them).

I know how to id it for last one:

public Long getLastDay(){
Cursor cursor= mDatabase.query(DatabaseHelper.TABLE_DAYS, new String[]{"MAX(" + DatabaseHelper.COLUMN_DAY_ID + ")"}, null, null, null, null, null);
if (cursor != null) {
cursor.moveToFirst();
return (cursor.getLong(0));
}
return Long.parseLong("0");
}


For example for ids:

1 4 7 11 14 15 19 25 28


I want to get
28
(it works) and
25
.

How is it possible to get the second value?

@EDIT:

My code is:

public Long getSecondLastDay(){
Cursor cursor= mDatabase.rawQuery("SELECT "+DatabaseHelper.COLUMN_DAY_ID+" FROM " + DatabaseHelper.TABLE_DAYS+ " LIMIT 2", null);
if (cursor != null) {
cursor.moveToFirst();
return (cursor.getLong(0));
}
return Long.parseLong("0");
}

CL. CL.
Answer

Sort the days in the reverse direction, and then just take the first two rows:

Cursor cursor = mDatabase.query(DatabaseHelper.TABLE_DAYS,
        new String[]{ DatabaseHelper.COLUMN_DAY_ID },
        null, null, null, null,
        DatabaseHelper.COLUMN_DAY_ID + " DESC",
        "2");
long secondToLastDay = 0, lastDay = 0;
if (cursor.moveToFirst()) {
    lastDay = cursor.getLong(0);
    if (cursor.moveToNext())
        secondToLastDay = cursor.getLong(0);
}