TOP of the Mornin’ to You!

Sometimes you find yourself stuck in a rut and you never even know that there is a rut. That’s where I found myself today my friends!

For years – and I do mean years – when I’ve been asked for the first 10 rows from a query, I would do the following:

SELECT TOP 10 * FROM dbo.MockPersonData

Does it work? Yes, absolutely.

But what about if you need to update only the top 250 records or, even more fun, delete the top 200 records (when ordered the correct way, obviously.)

I get an error doing this:

‘250’ is the error? Does it not know the business asked for that! What kind of nut came up with this?

So, this is fairly easy to fix. Just put parenthesis around the 250 and voila! You’ve fixed it!

Well, almost.

Since it’s not going to allow us to update what we think the top 250 is, then the ORDER BY, and therefore, the rest of the query is useless.

But, like all good data people, there is another way!

UPDATE dbo.Person1
SET FirstName = 'Fred'
WHERE ID IN (SELECT TOP (250) ID FROM dbo.Person1 
             ORDER BY ID DESC)

This gives you the ORDER that you normally need and it allows you to have the correct usage of the TOP operator.

Next time, we’ll talk thru some more ways to use the TOP clause and how you will use it. Until then, have fun listenin’ to one of my favorites from House of Pain!