How to Keep Only the Top N Rows in a SQL Database Table?

Preserving the Top Rows: Deleting All But n from a Database Table in SQL

When faced with the task of trimming a table in a database, the need arises to retain only a specified number (n) of rows at the top while eliminating the rest. To effectively achieve this, SQL offers a straightforward solution.

The DELETE statement can be employed to remove all rows except for the top n. The syntax is as follows:

DELETE FROM TableName WHERE RowID NOT IN (SELECT TOP n RowID FROM TableName)

This query selects all rows from the TableName table, excluding those that appear in the subquery. The subquery retrieves the top n RowIDs from the table.

Example:

Consider a table named "Customers" with columns such as "CustomerID" and "CustomerName." To delete all but the top 10 customers based on "CustomerID," the following query can be used:

DELETE FROM Customers WHERE CustomerID NOT IN (SELECT TOP 10 CustomerID FROM Customers)

Note:

As Chris astutely observed, the downside of using the TOP clause in the subquery is its potential performance impact. For one-time operations, it may not be a significant concern, but for frequent use, alternative approaches should be considered.

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