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

possible duplicate of [Limit an sqlite Table's Maximum Number of Rows](http://stackoverflow.com/questions/2035670/limit-an-sqlite-tables-maximum-number-of-rows) — Graham Borland, Oct 23 '12 at 10:49

raina77ow · Accepted Answer · 2012-10-23T11:12:11.040

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)

@Addev Sorry, have written my query before seeing your edit. I hope this'll answer the second part (regarding the efficiency) still. ) — raina77ow, Oct 23 '12 at 11:07

score 3 · Answer 2 · edited May 23 '17 at 10:28

3

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

edited May 23 '17 at 10:28

Community

1
1

answered Oct 23 '12 at 10:51

Ahmad

12,336
6
48
88

But this will keep the 100 oldest entries, not the 100 top entries? – Steve McLeod Oct 23 '12 at 10:53

score 1 · Answer 3 · edited Oct 23 '12 at 10:53

1

Try this:

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

Asssume your ID column doesn't have duplicates

edited Oct 23 '12 at 10:53

Yaqub Ahmad

27,569
23
102
149

answered Oct 23 '12 at 10:49

Joe G Joseph

23,518
5
56
58

score 0 · Answer 4 · answered Oct 23 '12 at 10:52

0

you can try this

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

answered Oct 23 '12 at 10:52

SRIRAM

1,888
2
17
17

There is no TOP Clause in SQLite... Your sub query will return more than one Columns – Yaqub Ahmad Oct 23 '12 at 10:58

score 0 · Answer 5 · answered Jan 05 '19 at 19:54

The answers here are good approaches to deal with your situation. I wanted to add an answer to explain the source of your syntax error.

The ORDER and LIMIT clauses on DELETE are a compile-time option for sqlite. I just spent several hours learning this the hard way :D.

From https://www.sqlite.org/compile.html#enable_update_delete_limit:

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

This option enables an optional ORDER BY and LIMIT clause on UPDATE and DELETE statements.

If this option is defined, then it must also be defined when using the Lemon parser generator tool to generate a parse.c file. Because of this, this option may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website.

score -2 · Answer 6 · answered Oct 23 '12 at 10:49

Certain words such as SELECT, DELETE, or BIGINT [or ORDER] are reserved and require special treatment for use as identifiers such as table and column names.

Traditional MySQL quotes:

DELETE FROM ranking ORDER BY `score` DESC;

Proper (ANSI) SQL quotes (some databases support [order] as well):

DELETE FROM ranking ORDER BY "score" DESC;

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

Delete + order by in sqlite (Android)

6 Answers6

Linked