Using PARTITION BY and ORDER BY with a Common Table Expression
Since we need just the top entries in each category, it
would be nice to do something like this (using DENSE_RANK):
SELECT
Category,
Entrant,
DENSE_RANK() OVER (
PARTITION BY Category
ORDER BY Weight DESC
) AS DenseRank
FROM dbo.ContestResults
WHERE DenseRank <= 3
Well, that doesnt work (Invalid column name 'DenseRank'.).
I have seen people use table variables to store the results
and then SELECT from the results. The other option is to use a common table
expression. Here are the two versions of the query that do work:
Table Variable:
DECLARE @t TABLE (
Category varchar(10),
Entrant nvarchar(20),
DenseRank bigint
)
INSERT INTO @t (
Category,
Entrant,
DenseRank
)
SELECT
Category,
Entrant,
DENSE_RANK() OVER (
PARTITION BY Category
ORDER BY Weight DESC
) AS DenseRank
FROM dbo.ContestResults
SELECT *
FROM @t
WHERE DenseRank <= 3
ORDER BY Category, DenseRank, Entrant
Common Table Expression:
WITH t
AS (
SELECT
Category,
Entrant,
DENSE_RANK() OVER (
PARTITION BY Category
ORDER BY Weight DESC
) AS DenseRank
FROM dbo.ContestResults
)
SELECT
Category,
Entrant,
DenseRank
FROM t
WHERE DenseRank <= 3
ORDER BY Category, DenseRank, Entrant
Results of Either Query:
Category Entrant DenseRank
---------- ------------------- ---------
Pumpkin Chad Johnson 1
Pumpkin Dan Gardner 2
Pumpkin George Kopsell 2
Pumpkin John Suydam 3
Squash Dan Gardner 1
Squash Harvey Zale 1
Squash Terry Helmer 2
Squash Gary Spiel 3
Watermelon Mark Bardin 1
Watermelon Carlotta Giudicelli 2
Watermelon Christine Daaé 2
Watermelon Meg Giry 3
Watermelon Ubaldo Piangi 3
I think the CTE code is much simpler to read. It is
certainly more compact, though that does not always mean its easier to read or
maintain.
Execution Plan Differences (Table Variable versus CTE)
What about performance? Yes, I know this is a trivial
example, but the differences are interesting anyway.
The execution plans from these two queries show that the
table variable version is 60% of the batch, where the CTE version is 40% of the
batch. See the graphic plan below:
Using RANK() in the ORDER BY Clause
What if you just want to pull the top three entries in a
single category? Most examples use RANK()in the SELECT clause, but you can also
use RANK() [and DENSE_RANK()] in the ORDER BY clause.
Query:
SELECT TOP 3
Entrant,
Weight
FROM dbo.ContestResults
WHERE Category = 'Watermelon'
ORDER BY RANK() OVER (
PARTITION BY Category
ORDER BY Weight DESC
)
Result:
Entrant Weight
------------------- -----------
Mark Bardin 146
Carlotta Giudicelli 139
Christine Daaé 139
An important point here is that you would only want to use
ranking functions in the ORDER BY clause of a query if you are using PARTITION
BY. If you left out the PARTITION BY, you would be left with a needlessly complicated
ORDER BY clause. These two queries illustrate this. They produce the exact same
result.
SELECT ...
FROM dbo.ContestResults
ORDER BY DENSE_RANK() OVER (ORDER BY Weight DESC)
SELECT ...
FROM dbo.ContestResults
ORDER BY Weight DESC
See the queries here
and the graphic plan below.
Using RANK() in the SELECT and ORDER BY Clauses
It is interesting to note that, based on the query plan,
including the Rank in the SELECT list does not incur a penalty. The work is
done once and used twice.
Query:
SELECT TOP 3
Entrant,
Weight,
RANK() OVER (
PARTITION BY Category
ORDER BY Weight DESC
) AS [Rank]
FROM dbo.ContestResults
WHERE Category = 'Watermelon'
ORDER BY RANK() OVER (
PARTITION BY Category
ORDER BY Weight DESC
)
Result:
Entrant Weight Rank
------------------- ------ ----
Mark Bardin 146 1
Carlotta Giudicelli 139 2
Christine Daaé 139 2
See the queries here
and the graphic plan below.
Conclusion
My plan was to cover all of the ranking functions in this
article, but once I got started, there was just too much interesting stuff to
cover. Next month, I will cover NTILE() and ROW_NUMBER().
A Challenge
It is possible to use RANK() with different PARTITION
or ORDER BY in the SELECT clause and ORDER BY clauses, but I was unable to come
up with something that actually produced worthwhile results. Using the sample
table and data presented here, can you come up with a worthwhile SELECT
statement that uses RANK() or DENSE_RANK() in both the SELECT and ORDER BY
clauses where they dont match? If you can, submit your query and an
explanation through
the forum.
»
See All Articles by Columnist Rob Garrison