XML Feeds
In this blog post I will show you a couple of ways of getting the top 2 values from a set in SQL Server. The dense_rank queries will not work if you have SQL server 2000 but the other ones should.
Let's say you have a table that has this data
100
100
100
99
99
95
95
90
You want to get the 2 highest amounts in that table the values 100 and 99, how would you do this?
Let's take a look at some posibillities, first create this table and populate it with data
- CREATE TABLE TestTies (id INT IDENTITY,SomeValue TINYINT)
- INSERT TestTies VALUES(100)
- INSERT TestTies VALUES(100)
- INSERT TestTies VALUES(100)
- INSERT TestTies VALUES(99)
- INSERT TestTies VALUES(99)
- INSERT TestTies VALUES(95)
- INSERT TestTies VALUES(95)
- INSERT TestTies VALUES(90)
Top 2 obviously will not work
- SELECT TOP 2 id, SomeValue
- FROM TestTies
- ORDER BY SomeValue DESC
output ----------------- id SomeValue 1 100 2 100
You also cannot use WITH TIES because that just brings the value 100
- SELECT TOP 2 WITH TIES id, SomeValue
- FROM TestTies
- ORDER BY SomeValue DESC
Output ----------------- id SomeValue 1 100 2 100 3 100
Here are a couple of ways that will work
For all the queries below the ouput will be this
Output ------------------ id SomeValue 1 100 2 100 3 100 4 99 5 99
The first one is by using the DENSE_RANK() function. The queries below are functionally identical, one is using a Common Table Expression while the other one is using a subquery
- --query 1
- WITH rankings AS (
- SELECT *,DENSE_RANK() OVER ( ORDER BY SomeValue DESC) AS Rank
- FROM TestTies)
- SELECT id, SomeValue FROM rankings
- WHERE Rank <=2
- --query 2
- SELECT id, SomeValue FROM (
- SELECT *,DENSE_RANK() OVER ( ORDER BY SomeValue DESC) AS Rank
- FROM TestTies) x
- WHERE Rank <=2
We can also use the MAX function twice like in the query below
- --query 3
- SELECT *
- FROM TestTies
- WHERE SomeValue >= (SELECT MAX(SomeValue)
- FROM TestTies
- WHERE SomeValue < (SELECT MAX(SomeValue)
- FROM TestTies))
Another option is to use distinct top 2 in a sub query
- --query 4
- SELECT *
- FROM TestTies
- WHERE SomeValue in(
- SELECT DISTINCT TOP 2 SomeValue
- FROM TestTies
- ORDER BY SomeValue DESC)
Finally in query 5 we do a running count, as you can see that looks complicated
- --query 5
- SELECT l.id, l.SomeValue
- FROM(SELECT v.SomeValue, v.id,
- Ranking = (SELECT COUNT(DISTINCT SomeValue)
- FROM TestTies a
- WHERE v.SomeValue <= a.SomeValue)
- FROM TestTies v) l
- WHERE l.Ranking <=2
- ORDER BY l.Ranking
So how do these queries perform in regards to each other?
Hit CTRL + K, select all the code in the code block below and hit F5/execute
- --query 1
- WITH rankings AS (
- SELECT *,DENSE_RANK() OVER ( ORDER BY SomeValue DESC) AS Rank
- FROM TestTies)
- SELECT id, SomeValue FROM rankings
- WHERE Rank <=2
- --query 2
- SELECT id, SomeValue FROM (
- SELECT *,DENSE_RANK() OVER ( ORDER BY SomeValue DESC) AS Rank
- FROM TestTies) x
- WHERE Rank <=2
- --query 3
- SELECT *
- FROM TestTies
- WHERE SomeValue >= (SELECT MAX(SomeValue)
- FROM TestTies
- WHERE SomeValue < (SELECT MAX(SomeValue)
- FROM TestTies))
- --query 4
- SELECT *
- FROM TestTies
- WHERE SomeValue in(
- SELECT DISTINCT TOP 2 SomeValue
- FROM TestTies
- ORDER BY SomeValue DESC)
- --query 5
- SELECT l.id, l.SomeValue
- FROM(SELECT v.SomeValue, v.id,
- Ranking = (SELECT COUNT(DISTINCT SomeValue)
- FROM TestTies a
- WHERE v.SomeValue <= a.SomeValue)
- FROM TestTies v) l
- WHERE l.Ranking <=2
- ORDER BY l.Ranking
Here is the result
query 1 9.89% (dense_rank CTE)
query 2 9.89% (dense_rank sub query)
query 3 6.70% (max twice)
query 4 19.41% (distinct sub query)
query 5 54.11% (running count)
Wow, query 5 running count is slower than the other 4 combined, this was expected of course. It is also interesting that dense_rank is not as efficient as using max twice
Let's do some more testing, we will create a non clustered index on the SomeValue column
- CREATE INDEX ix_SomeValue ON TestTies(SomeValue DESC)
Run the 5 queries again
query 1 13.58% (dense_rank CTE)
query 2 13.58% dense_rank sub query)
query 3 24.03% (max twice)
query 4 13.95% (distinct sub query)
query 5 34.32% (running count)
As you can see now dense_rank is fastest. Let's make that non clustered index a clustered index and look at the plans again.
- DROP INDEX TestTies.ix_SomeValue
- CREATE CLUSTERED INDEX ix_SomeValue ON TestTies(SomeValue DESC)
Below are the results of running those queries again
query 1 7.78% (dense_rank CTE)
query 2 7.78% dense_rank sub query)
query 3 23.32% (max twice)
query 4 15.95% (distinct sub query)
query 5 45.16% (running count)
As you can see when we have an index on the column then dense_rank is the fastest out of all. Feel free to load up some more data into the table and experiment with these queries. If you know of another way to accomplish this feel free to post a comment with your query.
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.