<p>What's the best way to delete all rows from a table in sql but to keep n number of rows on the top?  </p>

[{'answer_id': 46386, 'author': 'Cory Foy', 'author_id': 4083, 'author_profile': 'https://Stackoverflow.com/users/4083', 'pm_score': 7, 'selected': True, 'text': '<pre><code>DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)\n</code></pre>\n\n<p><strong>Edit:</strong> </p>\n\n<p>Chris brings up a good performance hit since the TOP 10 query would be run for each row. If this is a one time thing, then it may not be as big of a deal, but if it is a common thing, then I did look closer at it.</p>\n'}, {'answer_id': 46388, 'author': 'Mark Biek', 'author_id': 305, 'author_profile': 'https://Stackoverflow.com/users/305', 'pm_score': 2, 'selected': False, 'text': '<p>I don\'t know about other flavors but MySQL <a href="http://dev.mysql.com/doc/refman/5.0/en/delete.html" rel="nofollow noreferrer">DELETE</a> allows LIMIT.</p>\n\n<p>If you could order things so that the n rows you want to keep are at the bottom, then you could do a DELETE FROM table LIMIT tablecount-n.</p>\n\n<p><strong>Edit</strong></p>\n\n<p>Oooo.  I think I like <a href="https://stackoverflow.com/questions/46385/delete-all-but-top-n-from-database-table-in-sql#46386">Cory Foy\'s</a> answer better, assuming it works in your case.  My way feels a little clunky by comparison.</p>\n'}, {'answer_id': 46393, 'author': 'Riri', 'author_id': 298, 'author_profile': 'https://Stackoverflow.com/users/298', 'pm_score': 0, 'selected': False, 'text': '<p>I would solve it using the technique below. The example expect an <em>article</em> table with an <em>id</em> on each row.</p>\n\n<pre><code>Delete article where id not in (select top 1000 id from article)\n</code></pre>\n\n<p>Edit: Too slow to answer my own question ...</p>\n'}, {'answer_id': 46404, 'author': 'Chris Miller', 'author_id': 206, 'author_profile': 'https://Stackoverflow.com/users/206', 'pm_score': 5, 'selected': False, 'text': '<p>I would select ID column(s) the set of rows that you want to keep into a temp table or table variable.  Then delete all the rows that do not exist in the temp table.  The syntax mentioned by another user:</p>\n\n<pre><code>DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)\n</code></pre>\n\n<p>Has a potential problem.  The "SELECT TOP 10" query will be executed for each row in the table, which could be a huge performance hit.  You want to avoid making the same query over and over again.</p>\n\n<p>This syntax should work, based what you listed as your original SQL statement:</p>\n\n<pre><code>create table #nuke(NukeID int)\n\ninsert into #nuke(Nuke) select top 1000 id from article\n\ndelete article where not exists (select 1 from nuke where Nukeid = id)\n\ndrop table #nuke\n</code></pre>\n'}, {'answer_id': 46505, 'author': 'Shawn', 'author_id': 26, 'author_profile': 'https://Stackoverflow.com/users/26', 'pm_score': 0, 'selected': False, 'text': '<p>Refactored?</p>\n\n<pre><code>Delete a From Table a Inner Join (\n    Select Top (Select Count(tableID) From Table) - 10) \n        From Table Order By tableID Desc\n) b On b.tableID = A.tableID\n</code></pre>\n\n<p><em>edit: tried them both in the query analyzer, current answer is fasted (damn order by...)</em></p>\n'}, {'answer_id': 46514, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 0, 'selected': False, 'text': '<p>Better way would be to insert the rows you DO want into another table, drop the original table and then rename the new table so it has the same name as the old table</p>\n'}, {'answer_id': 46520, 'author': 'Simurr', 'author_id': 3478, 'author_profile': 'https://Stackoverflow.com/users/3478', 'pm_score': 4, 'selected': False, 'text': "<p>Future reference for those of use who don't use MS SQL.</p>\n\n<p>In PostgreSQL use <code>ORDER BY</code> and <code>LIMIT</code> instead of <code>TOP</code>.</p>\n\n<pre><code>DELETE FROM table\nWHERE id NOT IN (SELECT id FROM table ORDER BY id LIMIT n);\n</code></pre>\n\n<p>MySQL -- well...</p>\n\n<blockquote>\n  <p><strong>Error</strong> -- This version of MySQL does not yet support 'LIMIT &amp;\n  IN/ALL/ANY/SOME subquery'</p>\n</blockquote>\n\n<p>Not yet I guess.</p>\n"}, {'answer_id': 509158, 'author': 'Noah', 'author_id': 12113, 'author_profile': 'https://Stackoverflow.com/users/12113', 'pm_score': 2, 'selected': False, 'text': "<p>This really is going to be language specific, but I would likely use something like the following for SQL server.</p>\n\n<pre><code>declare @n int\nSET @n = SELECT Count(*) FROM dTABLE;\nDELETE TOP (@n - 10 ) FROM dTable\n</code></pre>\n\n<p>if you don't care about the exact number of rows, there is always</p>\n\n<pre><code>DELETE TOP 90 PERCENT FROM dTABLE;\n</code></pre>\n"}, {'answer_id': 13226312, 'author': 'Tim Wilson', 'author_id': 1734757, 'author_profile': 'https://Stackoverflow.com/users/1734757', 'pm_score': 3, 'selected': False, 'text': '<p>I think using a virtual table would be much better than an IN-clause or temp table.</p>\n\n<pre><code>DELETE \n    Product\nFROM\n    Product\n    LEFT OUTER JOIN\n    (\n        SELECT TOP 10\n            Product.id\n        FROM\n            Product\n    ) TopProducts ON Product.id = TopProducts.id\nWHERE\n    TopProducts.id IS NULL\n</code></pre>\n'}, {'answer_id': 55834751, 'author': 'Hrvoje', 'author_id': 2119941, 'author_profile': 'https://Stackoverflow.com/users/2119941', 'pm_score': 3, 'selected': False, 'text': '<p>Here is how I did it. This method is faster and simpler:</p>\n<p><strong>Delete all but top n from database table in MS SQL using OFFSET command</strong></p>\n<pre><code>WITH CTE AS\n    (\n    SELECT  ID\n    FROM    dbo.TableName\n    ORDER BY ID DESC\n    OFFSET 11 ROWS\n    )\nDELETE CTE;\n</code></pre>\n<p>Replace <code>ID</code> with column by which you want to sort.\nReplace number after <code>OFFSET</code> with number of rows which you want to keep.\nChoose <code>DESC</code> or <code>ASC</code> - whatever suits your case.</p>\n'}, {'answer_id': 63455642, 'author': 'clamchoda', 'author_id': 591285, 'author_profile': 'https://Stackoverflow.com/users/591285', 'pm_score': 0, 'selected': False, 'text': "<p>I've got a trick to avoid executing the <code>TOP</code> expression for every row. We can combine <code>TOP</code> with <code>MAX</code> to get the <code>MaxId</code> we want to keep. Then we just delete everything greater than <code>MaxId</code>.</p>\n<pre><code>-- Declare Variable to hold the highest id we want to keep. \nDECLARE @MaxId as int = (\nSELECT MAX(temp.ID)\nFROM (SELECT TOP 10 ID FROM table ORDER BY ID ASC) temp\n)\n\n-- Delete anything greater than MaxId. If MaxId is null, there is nothing to delete.\nIF @MaxId IS NOT NULL\n    DELETE FROM table WHERE ID &gt; @MaxId\n</code></pre>\n<p>Note: It is important to use <code>ORDER BY</code> when declaring <code>MaxId</code> to ensure proper results are queried.</p>\n"}]
['https://Stackoverflow.com/questions/46385', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/298/']