How do I delete all but the newest 20 rows in a table?

How do you delete the top row? I want to delete the top row if theres more 开发者_如何学JAVAthan 20 rows. im using c#.

---

Have a look at DELETE (Transact-SQL)

TOP (expression) [ PERCENT ]

Specifies the number or percent of random rows that will be deleted. expression can be either a number or a percent of the rows. The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

Maybe also have a read at The DELETE statement in SQL Server

---

Some info about deletion http://msdn.microsoft.com/en-us/library/bb386925.aspx

var ordFetch =
    (from ofetch in db.Orders
     where ofetch.OrderID == reqOrder
     select ofetch).First();
db.Orders.DeleteOnSubmit(ordFetch);
db.SubmitChanges();

To select top rows:

var data = (from p in people
           select p).Take(100);

---

Assuming the ID field is a suitable substitute and always increases with time (i.e., smallest values of ID are the oldest, and that is always the case):

using (SqlConnection conn = new SqlConnection("yourconnectionstring"))
{
    SqlCommand cmd = conn.CreateCommand(
        "DELETE FROM table WHERE ID NOT IN (SELECT TOP 20 ID FROM table ORDER BY ID DESC)");
    cmd.ExecuteNonQuery();
}

(My C# syntax may be slightly off -- I did this from memory -- but the SQL query should be fine.)