SQL Excluding Record sets

Posted At : July 14, 2011 9:45 AM | Posted By : Steve
Related Categories: SQL

I was eating with another speaker at cf.Objective() (I am resolved to find a way to work my having spoken at a conference into all future anecdotes) and an interesting SQL question came up: How to delete everything except the first 1000 records. To my mind, this brought up a general class of problems in SQL. Which is, returning results that exclude the result of a query.

With that in mind, let's look at a few of those.

[More]

Comments (4) | Print | del.icio.us | Digg It! | Linking Blogs
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment]
I was one of those developers who was unaware of EXCEPT and INTERSECT... but not any more thanks to your post Steve. Unfortunately they're not supported in MySQL, which might explain why they're not better known.

I'm also a fan of EXISTS for the same reason as you.
# Posted By Julian Halliwell | 7/17/11 3:50 AM
Nice post Steve,
I loved the subtle cf.objective reference :)
# Posted By John Whish | 7/17/11 8:39 AM
Julian,

I'm sorry to hear that MySQL doesn't support EXCEPT. I think it is been in the SQL standard for some time now.

John,

Thanks! Future anecdotes may have to resort to lame "Before I was a speaker at cf.Objective...", but I am not above that! ;-)
# Posted By Steve Bryant | 7/17/11 9:46 PM
if you're talking about not deleting 1000 queries and want to remove the rest - if the rest means actually times more rows than what you try to keep,
I'd go with copying those 1000 to a temp table, and run truncate command on the original table, of course only if you're granted to do that; truncate does not delete row after row, is times more faster;
# Posted By Lazeg | 11/4/11 11:50 AM
[Add Comment]
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.