How to Delete All but the Top N Rows from a SQL Database Table?

SQL Query to Delete All but Top n Rows from a Database Table

In many scenarios, it becomes necessary to remove all data from a database table except for the top n rows. This operation requires a carefully crafted query to ensure that only the desired number of rows is retained.

Recommended Approach:

The most efficient way to delete all but the top n rows from a table in SQL is to execute a query that selects the desired rows and then uses the DELETE statement to remove the remaining rows. The following query template demonstrates this approach:

DELETE FROM TableName WHERE ID NOT IN (
    SELECT DISTINCT TOP n ID
    FROM TableName
    ORDER BY ID
)

Advantages:

• This method ensures that only the top n rows are preserved in the table.
• It is relatively straightforward to implement and can be executed efficiently.

Example:

Consider a table named "Sales" with the following data:

ID	Name	Amount
1	John	100
2	Mary	200
3	Bob	300
4	Ann	400

To delete all but the top 2 rows from this table, we would use the following query:

DELETE FROM Sales WHERE ID NOT IN (
    SELECT DISTINCT TOP 2 ID
    FROM Sales
    ORDER BY ID
)

After executing this query, the "Sales" table would contain only the following rows:

ID	Name	Amount
1	John	100
2	Mary	200

The above is the detailed content of How to Delete All but the Top N Rows from a SQL Database Table?. For more information, please follow other related articles on the PHP Chinese website!