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

mysql delete all rows except one
delete all rows except one sql
delete last 10 rows in sql server
delete all except sql
delete last 10 rows in mysql
sql delete row if exists
sql query remove duplicate rows based on one column
delete from table

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.

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.

How to delete all rows except some in MySQL?, You can use NOT IN operator for the rows you do not want to delete. Following is Order by desc except a single value in MySQL delete from yourTableName where yourColumnName NOT N);. Let us first create a table − mysql> create table Here is the query to delete all rows with some condition. How to delete all rows from a table except the Top (N) rows, for example, leaving only the top (1000) rows? This article is the right answer to such questions and others like this one. All Code samples in this article are downloadable from this link. Problem. To explore this scenario we need a test database.

If you don't have an id field, i suppose you use an alphabetic order.

MYSQL
DELETE FROM friends 
WHERE friends_name 
NOT IN (
    SELECT * FROM (
        SELECT friends_name 
        FROM friends 
        ORDER BY friends_name ASC
        LIMIT 10) r
)

You delete all rows exept the 10 firsts (alphabetic order)

T-SQL: Delete All Rows From a Table Except Top(N), resulting in deleting more or fewer rows than 5, you could use this, which will be more accurate but probably slower with a big table: DELETE p FROM pixels  (75 rows affected) 4) Delete all rows from a table example. The following DELETE statement removes all rows from the product_history table: DELETE FROM production.product_history; Note that if you want to remove all rows from a big table, you should use the TRUNCATE TABLE statement which is faster and more efficient.

I just wanted to follow up on this relatively old question because the existing answers don't capture the requirement and/or are incorrect. The question states the names can be repeated, but only the top N must be preserved. Other answers will delete incorrect rows and/or incorrect number of them.

For example, if we have this table:

|friends_name|
==============
| Arunji     |
| Roshit     |
| Misbahu    |
| Misbahu    |
| Roshit     |
| Misbahu    |
| Rohan      |

And we want to delete all but top 3 rows (N = 3), the expected result would be:

|friends_name|
==============
| Arunji     |
| Roshit     |
| Misbahu    |

The DELETE statement from the currently selected answer will result in:

|friends_name|
==============
| Arunji     |
| Misbahu    |
| Misbahu    |
| Misbahu    |

See this sqlfiddle. The reason for this is that it first sorts names alphabetically, then takes top 3, then deletes all that don't equal that. But since they are sorted by name they may not be the top 3 we want, and there's no guarantee that we'll end up with only 3.

In the absence of unique indexes and other fields to determine what "top N" means, we go by the order returned by the database. We could be tempted to do something like this (substitute 99999 with however high number):

DELETE FROM names LIMIT 99999 OFFSET 3

But according to MySQL docs, while the DELETE supports the LIMIT clause, it does not support OFFSET. So, doing this in a single query, as requested, does not seem to be possible; we must perform the steps manually.

Solution 1 - temporary table to hold top 3
CREATE TEMPORARY TABLE temp_names LIKE names;
INSERT INTO temp_names SELECT * FROM names LIMIT 3;
DELETE FROM names;
INSERT INTO names SELECT * FROM temp_names;

Here's the sqlfiddle for reference.

Solution 2 - new table with rename
CREATE TABLE new_names LIKE names;
INSERT INTO new_names SELECT * FROM names LIMIT 3;
RENAME TABLE names TO old_names, new_names TO names;
DROP TABLE old_names;

Here's the sqlfiddle for this one.

In either case, we end up with top 3 rows in our original table:

|friends_name|
==============
| Arunji     |
| Roshit     |
| Misbahu    |

MySQL: Delete all but last N records, First, you specify the name of the table from which the rows are to be deleted in the For example, the following statement will delete all rows from the target_table : you can delete the 10 percent of random rows by using the following DELETE creates a table named production.product_history with the data copied from  Delete rows based on duplicates in one column with Remove Duplicates feature This method will introduce the Remove Duplicates feature to remove entire rows based on duplicates in one column easily in Excel. 1. Select the range you will delete rows based on duplicates in one column, and then click Data > Remove Duplicates.

SQL Server DELETE, The MySQL DELETE LIMIT statement is used to delete records from a table in how to use the DELETE LIMIT statement in MySQL with syntax and examples. For example, LIMIT 10 would delete the first 10 rows matching the delete criteria. DELETE LIMIT statement since you are deleting the entire row from the table. Delete all rows from Table except First (Header) row using JavaScript The following HTML Markup consists of an HTML Table with a Header row and three other rows and a Button. The Button has been assigned a JavaScript OnClick event handler. When the Button is clicked, the DeleteRows JavaScript function is executed.

MySQL: DELETE LIMIT Statement, This article will show you method of deleting all rows except the first header blank rows in selected range, active sheet, selected sheets or all sheets with One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns. Combine Workbooks and WorkSheets; Merge Tables based on key columns;  185. The above example contains the button and the table with rows. Click the above button to delete all table rows except first one. The first row is the main heading for the content of the table. The slice () requires one argument as the numeric to remove rows. If the value is 1, it removes all rows except first.

How to delete all rows except the first header row in Excel?, How to delele all rows except some for each group in a MySQL table. with one comment Assume I have a table foo where I have something like this: What I want to do is remove all rows that aren't the newest N per user. Unfortunately, MySQL doesn't allow multiple columns in a subquery used in a  Delete all rows except the first header row with VBA code Easily delete all blank rows in selected range/sheets or active/all sheet(s) in Excel: As below screenshot, you can delete all blank rows in selected range, active sheet, selected sheets or all sheets with only several clicks in Excel.

Comments
  • You don't have an id field ??
  • @ImaneFateh No.. no id field.
  • Can you reverse the order and put the top N as last N ? if so you can use limit...
  • Can't able to see the fiddle.
  • It will be help full if you explain it @fthiella . I don't understand why SELECT * FROM (...) s is used?
  • @Sp4Rx the double subquery is a workaround because mysql doesn't allow limit in a subquery