Delete all rows except first N from a table having single column

Question

I need a single query. Delete all rows from the table except the top N rows. The table has only one column. Like,

|friends_name|
==============
| Arunji     |
| Roshit     |
| Misbahu    |
| etc...     |

This column may contain repeated names as well.

• Contains repeated names

• Only one column.

Answer 1

If you can order your records by friends_name, and if there are no duplicates, you could use this:

DELETE FROM names
WHERE
  friends_name NOT IN (
    SELECT * FROM (
      SELECT friends_name
      FROM names
      ORDER BY friends_name
      LIMIT 10) s
  )

Please see fiddle here.

Or you can use this:

DELETE FROM names ORDER BY friends_name DESC
LIMIT total_records-10

where total_records is (SELECT COUNT(*) FROM names), but you have to do this by code, you can't put a count in the LIMIT clause of your query.