SQL Rank Vs Row Number

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Rank Vs Row Number is a common question because the two functions are very similar. SQL Server has the two similar built-in functions to assign numbers to the order of the data in the result set. The SQL Server RANK function and the ROW_NUMBER function both can accomplish this, but they do something slightly different. You can follow these links see details on how to use this functions at RANK function and ROW_NUMBER function.

ROW_NUMBER: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will continue the sequence on the duplicated record… ensuring that the row number sequence is never duplicated.

RANK: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will use the same number for the duplicate records. There will then be a gap in the sequence for the duplicated records.

 
Reference:
    RANK: http://msdn.microsoft.com/en-us/library/ms176102.aspx
    ROW_NUMBER: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Ranking Data In SQL Server

Facebooktwittergoogle_plusredditpinterestlinkedinmail

When it comes to ranking data in SQL Server, as of SQL Server 2005 they have built this in to the database. In 2005 the SQL Server team introduced the RANK function. The RANK function allows you to assign a number to each record in a result set based something in the data.

Let’s say that you wanted to apply a rank based on the weight of animals in your zoo (from heaviest to lightest). Elephant would get a number of 1 (because it’s the heaviest) and a tiny turtle would get the number 100 (because it’s the lightest). The number returned would start at 1 and grow for every record. This is done using this awesome RANK function.

The RANK function also has a partition feature that allows you to group the data. This allows the ranking number to reset for every group. Using our example above, we could extend it to show us the heaviest to lightest animals based on the animal type. So there would be a #1 ranking for the mammal, amphibian, bird, and reptile animal types.

 
Duplicate Ranks
Because the ranking number is incremented in the order specified by the ORDER BY clause, what happens if there are duplicates? In our example above, what if there are 2 animals with the same weight? If this occurs, the same ranking number will be applied to each of the duplicate records. So if there were 2 animals with the same weight, they would get the same exact ranking number.

 
How To Use RANK
The RANK function has 2 parameters. You must always supply the ORDER BY. The PARTITION BY is optional.

    PARTITION BY – This is what you would like SQL Server to group your rankings by. In the above example, if we want the ranking number to reset for every different animal types, then we would specify that here.
    ORDER BY – The ORDER BY is the order that you want your ranking number to be generated in your result set. This is a required parameter. In the example above, we want it from heaviest to lightest. We would specify the column name here that holds the animal weight.

Here is some sample code and the output to show each of the above scenarios.

In the above example, you can see that we are ranking each record by the weight of the animal from heaviest to lightest. This is done by passing the ORDER BY parameter to the function.

AnimalID AnimalName AnimalWeight AnimalWeightRank
168 Camel 1200 1
162 Carabao 500 2
163 Cattle 400 3
166 Alpaca 350 4
182 Deer 300 5
187 Turkey 30 6
186 Goose 15 7
185 Duck 10 8
171 Cat 10 8
188 Quail 10 8
189 Pigeon 5 11
184 Chicken 5 11

 
 

In the above example, you can see that we are ranking each record by the weight of the animal from heaviest to lightest. We take this a bit further by grouping these by the AnimalType. This is done by passing the PARTITION BY parameter to the function call.

AnimalID AnimalName AnimalType AnimalWeight AnimalWeightRank
187 Turkey Bird 30 1
186 Goose Bird 15 2
185 Duck Bird 10 3
188 Quail Bird 10 3
189 Pigeon Bird 5 5
184 Chicken Bird 5 5
168 Camel Mammal 1200 1
162 Carabao Mammal 500 2
163 Cattle Mammal 400 3
166 Alpaca Mammal 350 4
182 Deer Mammal 300 5
171 Cat Mammal 10 6

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms176102.aspx

ROW_NUMBER – How To Use It

Facebooktwittergoogle_plusredditpinterestlinkedinmail

ROW_NUMBER is a function built-in to SQL Server that will return a row number for each record in your result set. You can further change resulting row number to reset the row number based on some value in the result set. I will show you examples of both below.

ROW_NUMBER Basics
To show the row number in SQL Server, you need to use the ROW_NUMBER function. This function is broken down in to two parts.

  • PARTITION BY – If you supply this parameter, then the row number will reset based on the value changing in the columns supplied. This is kinda like using a GROUP BY.
  • ORDER BY – This is the specified order of the row number that you would like. If you wanted the order of the row number to increment by an employee name (alphabetically), you do that here.

 
With this function I think examples will help paint the full picture.

In the following query, the results will show a row number with each record. The number will start at 1 and increment for every record in order of AnimalName. You can see that that the order of the row number increment is specified as a parameter in to the ROW_NUMBER() function.

AnimalID AnimalName AnimalType RowNumber
166 Alpaca Mammal 1
168 Camel Mammal 2
162 Carabao Mammal 3
171 Cat Mammal 4
163 Cattle Mammal 5
184 Chicken Bird 6
182 Deer Mammal 7
185 Duck Bird 8
186 Goose Bird 9
189 Pigeon Bird 10
188 Quail Bird 11
187 Turkey Bird 12

 
In the above example, the row number never reset. It started at 1 and kept going through all the records. But what if you wanted to reset the row number back to 1 based on a value changing in your result set. In the following example, every time that the AnimalType changes we will reset the row number back to 1. This way, each AnimalType would have it’s own set of row numbers. We accomplish this by listing the columns that we want to group the row numbers by in the PARTITION BY parameter.

AnimalID AnimalName AnimalType RowNumber
184 Chicken Bird 1
185 Duck Bird 2
186 Goose Bird 3
189 Pigeon Bird 4
188 Quail Bird 5
187 Turkey Bird 6
166 Alpaca Mammal 1
168 Camel Mammal 2
162 Carabao Mammal 3
171 Cat Mammal 4
163 Cattle Mammal 5
182 Deer Mammal 6

 
 
Good Things To Know
You can specify multiple columns in the PARTITION BY and ORDER BY parameters by separating them with a comma.

You can specify ASC or DESC in the ORDER BY parameter if you like as well.

 
Reference: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Order By On A Union?

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Have you ever tried to add an ORDER BY clause to a UNION operator and have gotten this error message?

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘UNION’.

So what the heck is this and how do we fix it?

 
What The Error Says
The error basically says that you have an issue with your syntax. The ORDER BY clause should only be applied one time in your query. Logically SQL Server will pull the data requested from the two tables… then it will perform the sort operation on the data.

When people see the UNION operator, they think that it is combining two different result sets, thus they need two ORDER BY clauses. In fact, you only need to order the final result set. This is done by placing the ORDER BY command at the very end of the statement.

 

SQL Server Pagination

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Paging through data is something that developers need to do frequently.  Until SQL Server 2012, the best way to achieve this is with the ROW_NUMBER function… and let’s face it… that wasn’t the easiest/most elegant thing to use.  Our prayers have been answered in SQL Server 2012.  The SQL Server team has come out with a better way of doing pagination using the OFFSET FETCH clause.

 
The OFFSET FETCH Clause

The OFFSET FETCH clause allows the client application to pull only a specified range of records.  To implement pagination using the OFFSET FETCH clause, it takes two parts… the OFFSET and the FETCH. 🙂

NOTE: To use the OFFSET FETCH clause for pagination, you must use an ORDER BY clause as well.

 
OFFSET

This part tells SQL Server to skip N number of rows in the result set.

The above statement tells SQL Server to return all of the AnimalNames in the Animal table, but only return the names after the 50th.  So 51 and beyond.

 
FETCH

This part tells SQL Server how many records to return in the result set.  If you use FETCH, you always need to use OFFSET.

The above statement tells SQL Server to return the AnimalNames in the Animal table with row numbers 51-60.  The OFFSET clause tells it to skip 50 rows and the FETCH clause tells it to return 10 records.

 
Variable Row Counts

You are also able to use a variable for the record counts in the query.  If you wanted to do this, it would look like this:

 
 
Reference: http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx