Getting the TOP Performers

In the last blog post, we went over the extreme basics of using the TOP operator in SQL. We showed how to grab things like the TOP 10 of a certain item.

That ability will get you through a number of criteria that you will be asked to perform. But what if you’re asked to grab the top five percent of performers in your company? Or in a region? It’s kinda hard to do that if you only have what we know so far, right?

If we do a quick count of all of the records in our table dbo.Person1, we’ll notice that there are 550 records as shown below.

Then, we do the magic incantation:

SELECT TOP (10) PERCENT *
FROM dbo.Person1
ORDER BY ID

And just like that, we have 55 rows in our result!

If we change to another table, say dbo.Person2, with only changing the query to look at dbo.Person2 instead of dbo.Person1, we get 37 rows – since dbo.Person2 has 366 records in it! (Yes, that does mean that it will need to round at times as a normal human will do!)

Yes, you can even tell SQL Server to get all of your data by telling it to grab the TOP 100 PERCENT of the data.

SELECT TOP (100) PERCENT *
FROM dbo.Person2
ORDER BY ID

But tell it to provide you the top 110% of the data (because who doesn’t want more data) or the top -5% of the data (obvious mistyping error hopefully) and SQL Server will nicely provide you a helpful error message.

Now that you’ve seen some of the goodness that you can do with the TOP operator, next time, we’ll show off even more goodness that the TOP operator can help you with in your normal day-to-day routine. Until next time my friends!