ROW_NUMBER – How To Use It

by
Facebooktwitterredditpinterestlinkedinmail

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.

SELECT	AnimalID,
	AnimalName,
	AnimalType,
	ROW_NUMBER() OVER(ORDER BY AnimalName) AS RowNumber
FROM	Animal

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
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
[/table]

 
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.

SELECT	AnimalID,
	AnimalName,
	AnimalType,
	ROW_NUMBER() OVER(PARTITION BY AnimalType ORDER BY AnimalName) AS RowNumber
FROM	Animal A
ORDER	BY AnimalType, AnimalName

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,RowNumber
184,Chicken,Bird[attr style=”background-color:#F2F5A9″],1[attr style=”background-color:#F2F5A9″]
185,Duck,Bird,2
186,Goose,Bird,3
189,Pigeon,Bird,4
188,Quail,Bird,5
187,Turkey,Bird,6
166,Alpaca,Mammal[attr style=”background-color:#F2F5A9″],1[attr style=”background-color:#F2F5A9″]
168,Camel,Mammal,2
162,Carabao,Mammal,3
171,Cat,Mammal,4
163,Cattle,Mammal,5
182,Deer,Mammal,6
[/table]

 
 
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

39 thoughts on “ROW_NUMBER – How To Use It”

  1. I never understood the Row_Number() function until this post. Best simple and brief explanation that I found online.

    Reply

  2. 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…

    Reply

  3. 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.

    Reply

    • Hi Jessica. Yes you can select just one row number. Try using a sub query. Something like this:

      SELECT	AnimalID,
	AnimalName,
	AnimalType
FROM (	SELECT	AnimalID,
			AnimalName,
			AnimalType,
			ROW_NUMBER() OVER(ORDER BY AnimalName) AS RowNumber
		FROM	Animal) SubQuery
WHERE	SubQuery.RowNumber = 1
      Reply

  5. i have used it numerous times, without knowing what really it is.
    Thank you very much for the post…now i understood ROW_NUMBER() 🙂

    Reply

  7. 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

    Reply

    • 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.

      Reply

  9. 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?

    Reply

    • 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).

      Reply

  11. Nice tutorial, thanks for posting this. Now i become master in make query of ROW_NUMBER and Rank.
    Thanks Again………….

    Reply

  13. I never knew partition by and row_number is that simple to learn before this.
    It was great to learn this concept from here..!!!!

    Reply

  17. Hi Robert, can we include multiple columns in ‘partition by’ clause?
    If yes, how does that work?
    Thanks in advance!

    Reply

    • Yes, it’s easy. Just put a comma between the fields. For example: 

      ROW_NUMBER() OVER(PARTITION BY AnimalType, AnimalColor ORDER BY AnimalName) AS RowNumber

      Doing this will restart the RowNumber after every change in AnimalType/AnimalColor combination.

      Reply

  18. 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!!!!!!

    Reply

  19. 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

    Reply

    • 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.

      Reply

  20. 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!

    Reply

  22. 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!!

    Reply

  26. 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

    Reply

  27. 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

    Reply

Leave a Comment

CommentLuv badge