Delete + order in sqlite (Android)

Question

I have a table to save the ranking of my application with the following fields: [id,username,score] and I want to clear the table, saving only the top 100 entries.

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

 Error: near "ORDER": syntax error 

Another alternative that I considered:

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

but I don’t know if he is enough enough.

Answer 1

I assume you are looking for this:

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

Here's a SQL Fiddle illustrating the concept.

This is pretty efficient (actually, it's pretty typical), since the subquery is only executed once. Actually, it depends more on whether the “score” is covered by the 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 a built-in rowid field. Try the following:

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

You can learn more about it here.

Answer 3

Try the following:

 DELETE FROM ranking WHERE id NOT IN (SELECT id FROM ranking ORDER BY SCORE limit 100) 

Assume your id column has no duplicates

Answer 4

you can try this

 delete from ranking where id not in (select top(100) * from ranking order by score) 

Answer 5

Some words, such as SELECT, DELETE, or BIGINT [or ORDER], are reserved and require special processing to be used as identifiers, such as table and column names.

Traditional MySQL quotes:

 DELETE FROM ranking ORDER BY `score` DESC; 

Valid (ANSI) SQL quotes (some databases also support [order]):

 DELETE FROM ranking ORDER BY "score" DESC; 

Although I would consider renaming the column to avoid such confusing issues in the future.