How do I delete the last row of a SQLite table in Android. I don't have an _id column

Question

Okay, so I have a high score table. I have two columns, Player name and score.. Every time a new score is to be added to the table I delete the last row, put the new score and new player name in the last row and then sort the table according to the score. I can't delete the row with minimum score because there might be multiple entries with the same score and I don't want to delete all of them.

Answer 1

You might want to rebuild your table and include an id column with integer primary key autoincrement. You can do quite a bit with that column in place (here's an SO question you can look into for that).

Anyway I don't know how your process goes and why you need to delete the last row but here's an example of using an ID column to get the last row ( which I assume would be the latest insert and is what usually happens if you declare an ID integer primary key autoincrement column):

public int LastInsert() {
    SQLiteDatabase db = this.getReadableDatabase();
    final String MY_QUERY = "SELECT MAX(" + colID + ") FROM " + myTable;
    Cursor cur = db.rawQuery(MY_QUERY, null);
    cur.moveToFirst();
    int ID = cur.getInt(0);
    cur.close();
    return ID;
}

From here you can probably just get the result of LastInsert and use that to direct what your delete function should delete.

Imo you're better of maybe just updating the last row instead of deleting and reinserting in it's place though. Something like this :

public int UpdateAcc(Account acc) {

    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(colName, acc.getName());
    cv.put(colScore, acc.getScore());
    return db.update(myTable, cv, colID + "=?", new String[]{params});

}

Answer 2

I don't remember rather android with sqlite supports multiple commands per statement, but if so this might work:

DELIMITER ;;
SET @LastId = (SELECT ROWID FROM yourTable ORDER BY ROWID DESC LIMIT 1);;
DELETE FROM yourTable WHERE ROWID=@LastId;;

Otherwise you can store this in a integer variable:

SELECT ROWID FROM yourtable ORDER BY ROWID DESC LIMIT 1;

Then use that variable to run the next line

DELETE FROM yourtable WHERE ROWID=@ThatIntegerHere;