It’s been a while since I’ve covered a SQL 201 topic!  I want to jump back into it with ranking functions.  These are functions that let you represent orders in your data.  When you were in school I’m sure you had to take standardized tests.  When you did you were given a percentile score.  This score represented how you performed compared to all students in your sample.  This sample group was everyone in your grade level.  So if you were taking the end of course exams for 11th grade, you were compared with every other student in the 11th grade.

Percentile is just one of the four ranking functions you have available in SQL 2008 R2.  I’m going to go over how and when you would use these in the real world.  Let’s start with the easy stuff.

ROW_NUMBER()

I’m sure the first thing you did when you looked up ROW_NUMBER() was hit the msdn article, right?  If so, then you saw the syntax is:

   1: ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

It’s really not that tough.  Let’s run an example using the AdventureWorks database.  I want to see how our sales people are performing.  Let’s see how they are doing by their sales to date.

   1: SELECT 

   2:       ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'

   3:     , FirstName, LastName, SalesYTD

   4: FROM Sales.vSalesPerson

   5: WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

We call the ROW_NUMBER() function and tell the interpreter we want to know the row numbers in order of decreasing SalesYTD values.

Running the above query gives you the following results.

Makes sense right?  Well what if you wanted to see the order by Territory Name?  That’s where the partition_by_clause comes in.

   1: SELECT 

   2:       ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS 'Row Number'

   3:     , TerritoryName

   4:     , FirstName, LastName, SalesYTD

   5: FROM Sales.vSalesPerson

   6: WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

The partition by clause tells the interpreter when to start over.  In this case we want to start over every time there is a change in the TerritoryName.

The query above returns the following results.

It should be clear now, that every time you use a ranking function you have to include the order by clause.  The partition by clause is optional.  It’s similar to including an order by clause in a traditional select statement.  You have to tell the interpreter what order you want to use.

The ranking function needs that same information.

RANK()

Rank can be the exact same as ROW_NUMBER(), if there are no ties for the ORDER BY column.  But, if there are two rows with the same ORDER BY value, then they would share a RANK().  You can never have a tie when dealing with ROW_NUMBER().

Let’s update a record so we’ll have a tie in SalesYTD value.

   1: update Sales.vSalesPerson SET salesytd = 5200475.2313

   2: where 

   3:     (firstname = 'Jae' and lastname = 'Pak') 

   4:     or (firstname = 'Linda' and lastname = 'Mitchell');

   5: --now, let's see how ROW_NUMBER() and RANK() compare.

   6: SELECT 

   7:       ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'

   8:     , RANK() OVER(ORDER BY SalesYTD DESC) AS 'Rank'  

   9:     , FirstName, LastName, SalesYTD

  10: FROM Sales.vSalesPerson

  11: WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

I basically set two records to have the same SalesYTD.  Now, look at the results of the query.

Notice that row numbers one and two have the same rank, since they have the same rank?  That’s a tie.  But then in row 3, the rank jumps to 3?  When you use the RANK function, a tie removes the next counter. 

Basically if you have a tie for first, then there was no second place finisher.  If you want to keep the rank sequential, without losing any, then you want to use the DENSE_RANK() function.

DENSE_RANK()

If we add a DENSE_RANK() column, you can see what I mean about not skipping a rank.

   1: SELECT 

   2:       ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'

   3:     , RANK() OVER(ORDER BY SalesYTD DESC) AS 'Rank'  

   4:     , DENSE_RANK() OVER(ORDER BY SalesYTD DESC) AS 'Dense Rank'  

   5:     , FirstName, LastName, SalesYTD

   6: FROM Sales.vSalesPerson

   7: WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

Now we get the following results.

You can see RANK() skips over a rank of 2, but DENSE_RANK() does not.  I use RANK() more often thank DENSE_RANK(), but that’s only because in my uses, it’s a little like Ricky Bobby says, “If’ you’re not first, you’re last.”

Now that we have these first three ranking functions out of the way, let’s finish up with the example I gave first, percentile.

NTILE()

Since you might want to use a different number of groups, SQL Server gives you a function that takes a parameter for the number of groups you want to use to split up your data.

   1: NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )

If you want to set up a percentile, pass 100, SQL will then set up 100 groups, if you want quartiles, pass 4.  You tell sql the number of groups, and it’ll do it.  Most of the time I’ve used the function, it was always used to set up percentiles.  Since we only have a few rows to order, I’m going to use 4 groups. I want you to see how each row is split up.

Since we have four groups we divide the number of rows by four.  But we have 13 groups.  Lucky we have a tie for first place, we’ll give the first quartile the extra row.  But that may not always be true.  If there is a remainder when you take the number of rows in your result set and divide it by the number of ‘tiles’, then it will fill in the remainders from the first group to the last group.

Check out the remarks section in the msdn for NTILE(). 

Conclusion

Whenever you’re developing reports you’ll eventually need to do some kind of ranking on the data.  By knowing what these four functions can do, you’ll be able to slice and dice the data any number of ways your business users will request.

If you have any questions, let me know.  I’m here to help!