Sqlite3 How to delete last records using ORDER BY

Question

I’ve researched a lot in the inter but found no solution...

For example, I have an aptitude test. Hence I create a table: "Bdteste.db", with a bank: "candidates", with the fields: _id, name and notes.

I launch all the candidates first and only after the test, do I launch your grades.

I order the bank by the largest the smallest notes (ORDER BY DESC notes). But from a test with 20 candidates, I want to delete the last 15 grades, and take only the top 5.

So I did:

try {
     SQLiteDatabase dc = openOrCreateDatabase("DBTeste.db", MODE_PRIVATE, null);
     Cursor res = dc.rawQuery("SELECT * FROM candidatos ORDER BY notas DESC ", null);

     // Criei esta rotina abaixo para apagar os últimos registros, funciona, mas ela é muito grande
     int cont = 0, id = 0;          
     while (!res.isAfterLast()) {
         cont++;
         res.moveToNext();              
         if (cont > 15) {
            id = Integer.valueOf(res.getString(res.getColumnIndex("_id")));
            dc.execSQL("DELETE FROM candidatos WHERE _id = '" + String.valueOf(id) + "' ");
         }
     }

     res.close();
     dc.close();           
} catch (Exception e) {
     e.printStackTrace();
}

Can you delete this using a single line? For example, it could be like this:

dc.execSQL("DELETE FROM candidatos WHERE linhas > 15 "); 

that is, delete the last 15 records, which are the last 15 rows of the database...

I appreciate some help...

Answer 1

If you reverse the ordering order instead of the last will be the first ones you want to eliminate.

If you do, you can then use the LIMIT clause to get only the 15 records you want to delete.

DELETE FROM candidatos WHERE _id IN (SELECT _id FROM candidatos ORDER BY notas ASC LIMIT 15)