SQL: How can I limit a table to a given number of rows?

[DemonWasp]

Problem Description:

• The table will be checked by a separate program. The limit is known to that program, but not the database or database server.
  
• The records are to be deleted (or culled, or whatever) in reverse order by the ID field (which is autoincrementing). This will tend to delete older records.
  
• Ideally, no stored procedures will be used; just a single SQL command.
  

I've thought about "hackish" solutions like using select into to copy records I don't want to delete, or retrieving all records and then deleting only the ones I no longer want. However, both of these require a lot of communication for something that seems like it should be a "native" operation on a data set.

Any ideas?

[chrisbrown]

This might fall into the 'hackish' category, but it gets the job done. It's a MySQL dialect, so you may have to translate it.

Deletes all rows except for those with the 10 largest id's:

code:

DELETE FROM `TableName`         WHERE `Id` < (SELECT MIN(`Id`) FROM                 (SELECT `Id` FROM `TableName`                 ORDER BY (`Id`) DESC                 LIMIT 0, 10) AS `AnyAlias`);

[DtY]

code:

DELETE FROM `TableName` WHERE `Id` IN (SELECT `Id` FROM `TableName` ORDER BY `Id` DESC LIMIT 10)

Is a more readable method, and should work the same

e; If this query doesn't work, basically what you want to do is use a subquery to get a list of all the ids you want to delete, and then use DELETE FROM ... WHERE Id IN to do the deleting.

[chrisbrown]

WHERE `Id` NOT IN, but otherwise yeah, that is an improvement. My SQL (not MySQL) is patchy at best.

[DemonWasp]

Thanks everyone, that's exactly what I was looking for. I'm surprised that I couldn't find something so simple with a few quick Google searches, but I honestly spent at least half an hour Googling before I came here.

Does anyone know of a good SQL tutorial? I'm past the SELECT, DELETE, UPDATE, WHERE aspects, but I'm lost the instant I'd need a compound query like the ones above. I've gotten through the majority of my application development this co-op term without needing them, but I imagine they'd be useful to have for future jobs.

For reference, the syntax translated to Sybase SQL Anywhere, which is what I'm using:

code:

DELETE FROM [table] WHERE [id] NOT IN (SELECT TOP [maximum records] [id] FROM [table] ORDER BY [id] DESC)