There Has to be Room at the TOP!

In our continuing adventures with the TOP operator in SQL, we’ve seen it do a lot of things that probably aren’t obvious when you first think about using TOP – especially if you’ve been using SQL for a while.

Let’s say that our wonderful Product team asks us to get the top 1 % of people in our dbo.MockPersonData table. There are actually a few ways to get to this data, so let’s get right to it.

Remember that there are 1100 people in the table so, that means, that the top 1% of people in the table would be 11 people.

1. We can do the way that we reviewed in our previous post by using percentages:

SELECT TOP (1) PERCENT *
FROM dbo.MockPersonData
ORDER BY ID

2. We can do the calculations ourselves by figuring up the counts ourselves:

SELECT TOP (SELECT COUNT(1)/100 FROM dbo.MockPersonData) *
FROM dbo.MockPersonData
ORDER BY ID

3. We can use variables to figure up the counts ourselves:

DECLARE @PersonCount INT;

SELECT @PersonCount = COUNT(1)/100
FROM dbo.MockPersonData

SELECT TOP (@PersonCount) *
FROM dbo.MockPersonData
ORDER BY ID

All of these come up with the same answer – 11 records, all ordered by ID.

Be very careful when you’re working with either of these ways though because these ways can give different answers under certain circumstances.

For example, let’s do the same thing as above with the dbo.Person1 table – which has 550 rows in total.

With our first version of this, we get 6 rows. Since 550/100 is 5.5 persons, I can see where SQL Server gets 6 rows.

With our second version though, we get 5 rows instead of 6!

The reason is because of the way that SQL Server figures out the data type for the COUNT(1)/100. This is determined by the SQL Server engine to be an Integer – which is required for the TOP operator – so it uses its internal divisor to determine that the correct answer is 5. The first method, on the other hand, determines that the number of rows to return is 6 based on percentages.

I bring all of this up to warn you of differences you may see if you work with this data in different ways.

Or, as I have said many times when I answer questions when it comes to SQL: “It Depends”

And with that, I must leave you for today. I’ve shown you how to use a subquery in the TOP operator clause (option 2) I’ve shown you how to use variables with the TOP operator (option 3). And I’ve shown you something to be very careful when working with the TOP operator. Until next time my friends!