Get first and last primary key values from SQLlite db table in Java?

Question

I'm trying to create methods for my DatabaseHandler class in Java to get the minimum and maximum row IDs from a single table in my database. I'm new to SQLite (working with Android) so I'm not sure what to do from here:

public int getMaxId() {
    String countQuery = "SELECT last_insert_rowid() FROM " + TABLE_RESOURCES;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int maxId = <SOMETHING>
    return maxId;
}

(I assume I could just change the sql query to 'first_insert_rowid()' for the first row ID - but how do I get and return that int?)

More background info: My table is constantly being written to and deleted from, so row IDs sometimes go like '2,3,4,5,8,9,11,13', and so on - and I'm running a for loop starting at the lowest row id available and going all the way to the highest, and ignoring empty spaces.

EDIT: Based on the answers, something like this? Only it gives a CursorOutOfBoundsException...

public int getMaxId() {
    String countQuery = "SELECT MAX(rowid) FROM " + TABLE_RESOURCES;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int maxId = 0;
    if (cursor.moveToFirst()) {
        maxId = cursor.getInt(0);
        return maxId;
    } else
    {
        return maxId;
    }

}

http://stackoverflow.com/questions/11515165/sqlite3-select-min-max-together-is-much-slower-than-select-them-separately — Budius, Aug 27 '14 at 15:58
android.database.sqlite.SQLiteException: no such column: DESC (code 1): , while compiling: SELECT * FROM resources id ORDER BY DESC LIMIT 1 ... I think there's an error in the syntax... but thank you! This looks like it's very close to working now... — , Aug 27 '14 at 16:30
simply write String selectQuery = "SELECT * FROM tablename columnname ORDER BY ASC LIMIT 1"; — Indra Kumar S, Aug 27 '14 at 16:33
String selectQuery = "SELECT * FROM resources id ORDER BY DESC LIMIT 1"; --- still comes up with 'no such column: DESC'... — , Aug 27 '14 at 16:40

score 0 · Answer 1 · answered Aug 27 '14 at 15:58

0

Use MIN(rowid) and MAX(rowid) instead of last_insert_rowid() to get the min and max values for rowid in a table.

Use something like

if (c.moveToFirst()) {
    int maxId = cursor.getInt(0);
}

to get a value from a cursor's first row where 0 is the column index you're interested in.

Also, Android's built-in database engine is SQLite and not MySQL.

answered Aug 27 '14 at 15:58

laalto

150,114
66
286
303

I updated my post with your modifications - something like that? I get a CursorIndexOutOfBounds Exception... – Aug 27 '14 at 16:11

Indra Kumar S · Accepted Answer · 2014-08-27T16:33:44.353

0

Use Order by and LIMIT 1.

ID Order by ASC LIMIT 1 will give you minimum and ID Order by DESC LIMIT 1 will give maximum

 public int  getMinId(){
    int minId = null;
    String selectQuery = "SELECT * FROM " + TABLE_RESOURCES + " " + KEY_ID +" ORDER BY ASC LIMIT 1";

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery,null);

    cursor.moveToFirst();
    if(cursor.getCount() > 0){

        minId= cursor.getInt(0);

    }
    cursor.close();
    db.close();

    return minId;
}

edited Aug 27 '14 at 16:33

answered Aug 27 '14 at 15:58

Indra Kumar S

2,818
2
16
27

That could work! But how do I actually get the maxId integer out of the cursor? – Aug 27 '14 at 16:12

Get first and last primary key values from SQLlite db table in Java?

2 Answers2