# Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

## SQL Server 2005: Using PARTITION and RANK in your criteria

The RANK and PARTITION features in 2005 are simply amazing.  They make so many "classic" SQL problems very easy to solve.  For example, consider the following table:

create table Batting
(Player varchar(10), Year int, Team varchar(10), HomeRuns int, primary key(Player,Year))

insert into Batting
select 'A',2001,'Red Sox',13 union all
select 'A',2002,'Red Sox',23 union all
select 'A',2003,'Red Sox',19 union all
select 'A',2004,'Red Sox',14 union all
select 'A',2005,'Red Sox',11 union all
select 'B',2001,'Yankees',42 union all
select 'B',2002,'Yankees',39 union all
select 'B',2003,'Yankees',42 union all
select 'B',2004,'Yankees',29 union all
select 'C',2002,'Yankees',2 union all
select 'C',2003,'Yankees',3 union all
select 'C',2004,'Red Sox',6 union all
select 'C',2005,'Red Sox',9

Suppose we would like to find out which year each player hit their most home runs, and which team they played for.  As a tie-breaker, return the latest year.

In SQL 2000, we had to do it in two steps:  First, get the MAX(HomeRuns) per player, and then join back to the Batting table to return the rest of the data:

select b.*
from
batting b
inner join
(    select player, Max(HomeRuns) as MaxHR
from Batting
group by player
) m
on b.Player = m.player and b.HomeRuns = m.MaxHR

Player     Year        Team       HomeRuns
---------- ----------- ---------- -----------
A          2002        Red Sox    23
B          2001        Yankees    42
B          2003        Yankees    42
C          2005        Red Sox    9

(4 row(s) affected)

Note that for player 'B', we get two rows back since he has two years that tie for the most home runs (2001 and 2003).  How do we return just the latest year?  Guess what -- it requires *another* pass through the table:

select b.*
from
batting b
inner join
(    select player, Max(HomeRuns) as MaxHR
from Batting
group by player
) m
on b.Player = m.player and b.HomeRuns = m.MaxHR
inner join
(  select player, homeRuns, Max(Year) as MaxYear
from Batting
group by Player, HomeRuns
) y
on m.player= y.player and m.maxHR = y.HomeRuns and b.Year = y.MaxYear

Player     Year        Team       HomeRuns
---------- ----------- ---------- -----------
C          2005        Red Sox    9
A          2002        Red Sox    23
B          2003        Yankees    42

(3 row(s) affected)

An alternate way to do this is to calculate the "ranking" of each home run for each player, using a correlated subquery:

select b.*,
(select count(*) from batting b2 where b.player = b2.player and b2.HomeRuns >= b.HomeRuns) as Rank
from batting b

Player     Year        Team       HomeRuns    Rank
---------- ----------- ---------- ----------- -----------
A          2001        Red Sox    13          4
A          2002        Red Sox    23          1
A          2003        Red Sox    19          2
A          2004        Red Sox    14          3
A          2005        Red Sox    11          5
B          2001        Yankees    42          2
B          2002        Yankees    39          3
B          2003        Yankees    42          2
B          2004        Yankees    29          4
C          2002        Yankees    2           4
C          2003        Yankees    3           3
C          2004        Red Sox    6           2
C          2005        Red Sox    9           1

However, notice that we still have not handled ties! (notice that Player "B" has no #1 ranking, just two #2 rankings!) To do that, we must make things a little more complicated:

select b.*,
(select count(*) from batting b2 where b.player = b2.player and (b2.HomeRuns > b.HomeRuns or (b2.HomeRuns = b.HomeRuns and b2.Year >= b.Year))) as Rank
from batting b

Player     Year        Team       HomeRuns    Rank
---------- ----------- ---------- ----------- -----------
A          2001        Red Sox    13          4
A          2002        Red Sox    23          1
A          2003        Red Sox    19          2
A          2004        Red Sox    14          3
A          2005        Red Sox    11          5
B          2001        Yankees    42          2
B          2002        Yankees    39          3
B          2003        Yankees    42          1
B          2004        Yankees    29          4
C          2002        Yankees    2           4
C          2003        Yankees    3           3
C          2004        Red Sox    6           2
C          2005        Red Sox    9           1

(13 row(s) affected)

And, with that, we can use our "ranking" formula to return only the #1 rankings to get our results by moving the subquery to the WHERE clause:

select b.*
from batting b
where (select count(*) from batting b2 where b.player = b2.player and (b2.HomeRuns > b.HomeRuns or (b2.HomeRuns = b.HomeRuns and b2.Year >= b.Year))) =1

Player     Year        Team       HomeRuns
---------- ----------- ---------- -----------
C          2005        Red Sox    9
A          2002        Red Sox    23
B          2003        Yankees    42

(3 row(s) affected)

And that's all you need to do this in SQL 2000 ! Easy, right?   Hmmm ... well, maybe not!

The new ranking functions in SQL Server 2005 let us quickly calculate each row's ranking within a set based on a partition and an ordering. Think of the partition as almost like a GROUP BY, where you the use of the word "per" in your specifications often indicate which columns you are grouping or partitioning on.  Since we want to return the top Home Run year per Player, we partition on Player.  This means that the rows for each Player will get a value of 1-x for their ranking.  The ranking itself is based on HomeRuns, so we want to return the rank for each partition ORDER'ed BY HomeRuns from high-to-low (DESC).

Using the new features, in SQL 2005 we can use the following SELECT to return each year's Home Run ranking for each Player:

select Player, Year, HomeRuns, Rank() over (Partition BY Player order by HomeRuns DESC) as Rank
from
Batting

Now, like before, we have to deal with ties.  But now, it is much easier -- we just add a secondary sort.  Since we want to the latest year to rank higher, we just add "Year DESC" to our ORDER BY:

select Player, Year, HomeRuns,Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) as Rank
from
Batting

Much easier than the old way!  However, we are still not done.  The above SQL returns all years with the Ranking beside it.  We are only interested in returning all rows with a Rank of 1.  However, if we try:

select Player, Year, HomeRuns
from
Batting
where
Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) = 1

We get an error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

We can easily fix this by wrapping the SELECT in a derived table:

select * from
(
select Player, Year, HomeRuns,  Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) as Rank
from
Batting
) tmp
where Rank = 1

And there you go ... It's easy to read and edit, and only 1 reference to the table is needed!  Preliminary testing also indicates that this is much more efficient than doing things "SQL 2000-style".

Other challenging SQL statements to write in the pre-2005 world are also easily solved in SQL 2005.  For example, the classic SQL problem of returning the "top x per group" based on a sort, or returning a "row number" or sequence for an entire result set or groups within that set, are now very, very easy using the RANK() feature.  Try it out!