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

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

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

  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…

  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.

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

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

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

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

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

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

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

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

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

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

Leave a Comment

CommentLuv badge