-----------------------------------

DemonWasp

Tue Aug 17, 2010 2:42 pm


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

-----------------------------------

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

Tue Aug 17, 2010 3:26 pm


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

-----------------------------------

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`);[/code]


-----------------------------------

DtY

Tue Aug 17, 2010 9:54 pm


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

-----------------------------------

[code]DELETE FROM `TableName` WHERE `Id` IN (SELECT `Id` FROM `TableName` ORDER BY `Id` DESC LIMIT 10)[/code]

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

Tue Aug 17, 2010 10:12 pm


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

-----------------------------------

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


-----------------------------------

DemonWasp

Wed Aug 18, 2010 9:03 am


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

-----------------------------------

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)
[/code]