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.
|
1 2 3 4 5 |
SELECT AnimalID, AnimalName, AnimalType, ROW_NUMBER() OVER(ORDER BY AnimalName) AS RowNumber FROM Animal |
| 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 resets. 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.
|
1 2 3 4 5 6 |
SELECT AnimalID, AnimalName, AnimalType, ROW_NUMBER() OVER(PARTITION BY AnimalType ORDER BY AnimalName) AS RowNumber FROM Animal A ORDER BY AnimalType, AnimalName |
| 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
I never understood the Row_Number() function until this post. Best simple and brief explanation that I found online.
Thanks Robert. Thorough, and easy to understand with non drawn-out examples that get to the specific point of ROW_NUMBER(). Explanation and example of PARTITION BY is great also. Thanks again…
No problem! ROW_NUMBER is a very useful function in SQL Server, but can get confusing. Glad I could help!
Is it possible to select on a row number? For example – only select the rows where the rownumber = 1. I’m trying to implement this but am receiving an error that the RowNumber column is not valid.
Hi Jessica. Yes you can select just one row number. Try using a sub query. Something like this:
Excellent Example…!!!
i have used it numerous times, without knowing what really it is.
Thank you very much for the post…now i understood ROW_NUMBER() 🙂
No problem, glad I could help!
Thank you, this is well explained!
Thank you for the explanation. Special kudos for giving a simple and understandable explanation. I once used this as below and it took me a hell lot of time to get this right.
Here I am trying to order by multiple values by giving them a ranking using the case statements.
ROW_NUMBER()OVER
( PARTITION BY P_IDNO
ORDER BY
CASE
WHEN Status_CODE = ‘X’ THEN 0
WHEN Status_CODE = ‘Y’ THEN 1
WHEN Status_CODE = ‘Z’ THEN 2
WHEN Status_CODE = ‘W’ THEN 3
END
,Sequence_Num DESC
) AS ROW_CNT
By using case statement I was trying to control how the rows would be numbered. If one single P_IDNO has all 4 status_code (X,Y,Z,W) then the rows would be numbered in the order of X,Y,Z and W.
Thanks !
Can you also explain how this is used in xml?
I think the way that I would tackle that is to parse the XML in to a table variable and then use the Row_Number() function on top of the table variable. See this post for how to parse XML into a table variable. Once you do that, you should just be able to reference it like any other table as in this post. http://www.sqlserver.info/syntax/parse-xml-with-sql-server/
Hi, I have one question related to the PARTITION BY example. You are using “ORDER BY AnimalName” statement but the result is ordered by AnimalType. How would the RowNumber look like if the table would be really ordered by AnimalName and partitioned by AnimalName?
I think your confusion is the ORDER BY AnimalName that you are looking at. That isn’t the order of the returned recordset, it is the order that is taken when applying the rownumber. If you look at the second example, you will see there are actually 2 ORDER BY clauses in that statement. The one inside the ROW_NUMBER() OVER( ) function… ONLY applies to the order of the calculation that generates the rownumber. It has nothing to do with the order of the records that are returned. The order of the records returned are ordered by the second ORDER BY statement).he end of the SQL statement).
You post on ROW_NUMBER is very well explained, even a novice can understand it.
Thanks a lot 🙂
Nice tutorial, thanks for posting this. Now i become master in make query of ROW_NUMBER and Rank.
Thanks Again………….
Very Nice explanation…
Thank you very much
I never knew partition by and row_number is that simple to learn before this.
It was great to learn this concept from here..!!!!
Yes great explanation. I now understand this function! 🙂
Great! Glad I could help!
Excellent ! Thanks.
nice
Hi Robert, can we include multiple columns in ‘partition by’ clause?
If yes, how does that work?
Thanks in advance!
Yes, it’s easy. Just put a comma between the fields. For example:
Doing this will restart the RowNumber after every change in AnimalType/AnimalColor combination.
Thanks robert for the post. It was very helpful. Also I request you to post some of them like CTE’s, Other ranking functions, Temp table, main differences between CTE and Temp usage, when to go with CTE or Temp table with examples. That would be really great and appreciated.
Thanks again for the post!!!!!!
Hi Robert, thanks for the clear and concise explanation; excellent!
I was wondering if you could use 2 columns in the order by clause, and specify one ASC and one DESC. The business case I have for this is when 2 records have identical DATETIME values and the ROW_NUMBER needs to check another column to determine the proper order.
Example (In this case, the ‘Event Rank’ values should be reversed):
MRN Drug Event Id Event Timestamp Event Rank
12345 Drug 1 Complete 554044 4/12/17 18:48 1
12345 Drug 1 In Progress 554045 4/12/17 18:48 2
SELECT [MRN],
[Drug],
[Event],
[Id],
[Event Timestamp],
ROW_NUMBER() OVER (
PARTITION BY [MRN],
[Drug],
CONVERT(DATE, [Event Timestamp]) ORDER BY [Event Timestamp] DESC,
[Id] ASC
) AS [Event Rank]
Thank you!
Eric
Yeap, you can specify as many columns in the ORDER BY that you’d like. You can mix and match the DESC and ASC to however you want.
I’m not sure what you’re accomplishing in your project, but if you are using SQL Server 2012 they have a new clause called OFFSET-FETCH that could help with paging through data. It may also be something to look in to if you are paging through data on a website.
I share the sentiment expressed by several others already: did not really understand ROW_NUMBER() *until this blog post*! I was immediately able to apply the concept(s) described here to a vexing query I was having for which MIN/MAX was just not working. Specifically, I was in need of selecting the nth row of a table and I saw ROW_NUMBER() referenced in other pages as the solution but couldn’t make heads or tails of what they were talking about. In trying to get the second row of a group, for example, I was trying “MIN() + 1” but that wasn’t returning the correct row which matched the where clause. Now I understand the syntax and ROW_NUMBER() is indeed much clearer, elegant way of solving these queries. Thank you so much for this clarity!
Thank you !!!!
This is the best explanation for row number that i’ve seen online, thank you. I’ve been using the function for a while but this makes so much sense now, I was always doing the trial and error with syntax whenever I wanted to calculate metrics by grouping. Bookmarked!!
Excellent explanation, thank you.
Exceelent example
THANK YOU! I was struggling for a while!
Great Explanation, I was able to take this row number function and extract million of records. In addition this sample also gave me, some Ideas to implement in excel. Great explanation.
William
How to remove duplicates from row_number column and only have the row with maximum value of row_number?
I want an output like this :
AnimalID AnimalName AnimalType RowNumber
187 Turkey Bird 6
182 Deer Mammal 6
Agree with all the appreciative comments here. Thank you.
how can I do a counter in sql server from a fixed number?
Nice Explanation !