Computer Science Canada

SQL: How can I limit a table to a given number of rows?
Author:  DemonWasp [ Tue Aug 17, 2010 2:42 pm ]
Post subject:  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?
Author:  chrisbrown [ Tue Aug 17, 2010 3:26 pm ]
Post subject:  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`);
Author:  DtY [ Tue Aug 17, 2010 9:54 pm ]
Post subject:  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)


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.
Author:  chrisbrown [ Tue Aug 17, 2010 10:12 pm ]
Post subject:  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.
Author:  DemonWasp [ Wed Aug 18, 2010 9:03 am ]
Post subject:  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)

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy