Search code examples
mysqlsql-delete

Leave only first 50 records in SQL database and delete the rest

I have a table of scores, which is made of 2 fields: name and high score. Something like this:

----------------------- 
| name     | score    |
-----------------------
| John     | 2450     |
-----------------------
| Alice    | 2420     |
-----------------------
                         ... etc

I need to delete all the rows till the top 50 scores.

Is it possible without creating another temporary table ?

Solution

  • please try this

    delete from scores_tbl Where
id not in
(select * from
(select id from scores_tbl order by score desc limit 50)
 as temp)