CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Milap
Milap

Reputation: 145

Delete all SQLite rows NOT IN specific rows

I have a database for my leaderboard. Currently, I insert all scores into my leaderboard, and select the 5 highest scores to show on my app. I think it would take up too much room to never delete the other scores, so I would like to delete them. How can I do this?

Here's how I select the top 5 scores, ranked first by score and second by time if score is equal:

    public  Cursor gethmLeaderboard(SQLiteDatabase db){

    String[] columns = {TableInfo.LB_RANK, TableInfo.LB_SCORE, TableInfo.LB_TIME};

    Cursor c = db.query(TableInfo.TABLE_HM, null, null, null, null, null, TableInfo.LB_SCORE + " DESC, " + TableInfo.LB_TIME + " ASC", "5");
    return c;

}

Here's how I create my table:

public String CREATE_HMQUERY = "CREATE TABLE " + TableInfo.TABLE_HM + "("
        + TableInfo.LB_RANK + " INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 1 ," + TableInfo.LB_SCORE +
        " INT,"+ TableInfo.LB_TIME + " VARCHAR );";

I want to delete all rows NOT IN that query. How can I do that?

What I've tried:

public String DEL_ALLBUTES = "DELETE FROM " +
        TableInfo.TABLE_HM + " WHERE " +
        TableInfo.LB_RANK + " NOT IN (SELECT " +
        TableInfo.LB_RANK + " FROM " +
        TableInfo.TABLE_HM + " ORDER BY " +
        TableInfo.LB_SCORE + " DESC, " +
        TableInfo.LB_TIME + " ASC LIMIT 5);";

In this format:

db.rawQuery(DEL_ALLBUTES, null);

But when I check the database there are still tons of rows so it doesn't work.

Upvotes: 0

Views: 146

Answers (1)

Phantômaxx
Phantômaxx

Reputation: 38098

Use execSQL() for commands (everything which is not a SELECT).
And rawQuery() for queries (SELECT).

Upvotes: 1

Related Questions