Delete + order by in sqlite (Android)

Question

I have a table for saving the ranking of my app with the following fields: [id,username,score] and I want to clean the table keeping only the top 100 entries.

How can I do this delete? I've tried DELETE FROM ranking ORDER BY score DESC LIMIT 100,999999999) but it returns an error:

Error: near "ORDER": syntax  error

Other alternative I've considered is:

DELETE FROM ranking WHERE id NOT IN (SELECT id FROM ranking ORDER BY score 
  DESC LIMIT 100)

but I dont know if it is efficient enought

Answer 1

I suppose you're looking for this:

DELETE FROM ranking WHERE id NOT IN (
    SELECT id FROM ranking ORDER BY score DESC LIMIT 100);

Here's SQL Fiddle illustrating the concept.

It's quite efficient (in fact, it's quite typical), as the nested query is executed once only. It actually depends more on whether the 'score' is covered by index - or not:

(without index):

EXPLAIN QUERY PLAN DELETE FROM ranking WHERE id NOT IN (
   SELECT id FROM ranking AS ranking_subquery ORDER BY score DESC LIMIT 2);

--
selectid   order   from   detail
0          0       0      SCAN TABLE ranking (~500000 rows)
0          0       0      EXECUTE LIST SUBQUERY 0
0          0       0      SCAN TABLE ranking AS ranking_subquery (~1000000 rows)
0          0       0      USE TEMP B-TREE FOR ORDER BY

(after CREATE INDEX ts ON ranking(score);)

selectid   order   from   detail
0          0       0      SCAN TABLE ranking (~500000 rows)
0          0       0      EXECUTE LIST SUBQUERY 0
0          0       0      SCAN TABLE ranking AS ranking_subquery USING INDEX ts (~1000000 rows)

Answer 2

All rows have built-in field rowid. Try this:

DELETE FROM [tbl_names] WHERE rowid not in 
(select rowid from [tbl_name] order by score desc limit 100 )

You can read more about that here