Click here to Skip to main content
12,698,012 members (26,337 online)
Click here to Skip to main content
Add your own
alternative version

Stats

491.8K views
37 bookmarked
Posted

How to use ROW_NUMBER() to enumerate and partition records in SQL Server

, 31 Dec 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Use ROW_NUMBER() to enumerate and partition records in SQL Server.

I had a situation recently where I had a table full of people records, where the people were divided into families. The business logic that needed to be followed was that I had to assign a “Twin Code” to each record. This meant that for each family in the database, if two or more members were born on the same day they should be treated as twins. The twins should be assigned a number enumerating them in order of birth. If the member was not a twin, they should just receive the twin code of 1.

Here’s an example table:

PersonIDFamilyIDFirstNameLastNameDateOfBirth
11JoeJohnson2000-10-23 13:00:00
21JimJohnson2001-12-15 05:45:00
32KarlyMatthews2000-05-20 04:00:00
42KacyMatthews2000-05-20 04:02:00
52TomMatthews2001-09-15 11:52:00

There are lots of ways to achieve the desired result, but the simplest is to just use a simple SELECT statement combined with the ROW_NUMBER() function with a couple parameters as to how to number the rows!

ROW_NUMBER() provides you with the number of a row in a given recordset, where you provide details on how to number the records. For example, if I just had to number the records above based solely upon the date of birth (ignoring families) then I would use this query:

SELECT
     [PersonID]
    ,[FamilyID]
    ,[FirstName]
    ,[LastName]
    ,[DateOfBirth]
    ,ROW_NUMBER() over (ORDER BY DateOfBirth) AS Number
FROM
	People
ORDER BY
	PersonID

This just tells the ROW_NUMBER() function to order its numbering ascending by DateOfBirth. Notice that I apply an order myself later on in the query, which is different than the row_number() order. I would get these results:

PersonIDFamilyIDFirstNameLastNameDateOfBirthNumber
11JoeJohnson2000-10-23 13:00:003
21JimJohnson2001-12-15 05:45:005
32KarlyMatthews2000-05-20 04:00:001
42KacyMatthews2000-05-20 04:02:002
52TomMatthews2001-09-15 11:52:004

The number field that is assigned to each record is in the order of DateOfBirth.

Ordering my numbering by DateOfBirth is just half of the picture. I also need to “group” the records by the FamilyID. This is where a clause in T-, SQL that you might not be very familiar with comes into play: “PARTITION BY”. The PARTITION BY clause allows us to group the results within the call to ROW_NUMBER() without grouping them ourselves via a GROUP BY. It just tells the ROW_NUMBERR what groupings to use when it does its counting.

Here is our final SQL statement, which achieves the business logic we wanted to implement.

SELECT
       [PersonID]
     [FamilyID]
      ,[FirstName]
      ,[LastName]
      ,[DateOfBirth]
      ,ROW_NUMBER() over(PARTITION BY FamilyID,
                         CONVERT(NVARCHAR(25), DateOfBirth, 111)
                         ORDER BY DateOfBirth ASC) TwinCode

  FROM [People]
ORDER BY	PersonID

IIn the ROW_NUMBER function above, I am doing several things. I’m grouping on FamilyID, and also grouping on a converted DateOfBirth. I convert the DateOfBirth to an nvarchar using the 111 conversion code, because that gets results like ‘2009/10/11′ and ‘2009/10/12′ which can easily be grouped by to achieve distinct dates.

Grouping on the Family, DateOfBirth, and then sorting by DateOfBirth ascending achieves the desired result for the ROW_NUMBER. Here are the results of the query:

PersonIDFamilyIDFirstNameLastNameDateOfBirthTwinCode
11JoeJohnson2000-10-23 13:00:001
21JimJohnson2001-12-15 05:45:001
32KarlyMatthews2000-05-20 04:00:001
42KacyMatthews2000-05-20 04:02:002
52TomMatthews2001-09-15 11:52:001

As you can see, the two people who qualify as twins above (Karly and Kacy) are enumerated correctly, with Karly receiving a 1 and Kacy receiving a 2. All the records that are not twins properly receive a 1.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dave C Andrews
Software Developer (Senior) LBMC Technologies
United States United States
I've been developing software since I was 9 years old, in BASIC on a Precomputer 2000. OK, that's a stretch, but hopefully some of the stuff that I learned from that little toy have helped my professional career.

I have a bachelors degree in computer science from MTSU. Currently, I am employed as a senior software developer for LBMC in Nashville, TN. We serve as consultant developers for clients across the southeastern United States, developing mostly in Microsoft .Net with Microsoft SQL Server.

I greatly enjoy my job and my hobbies lie in open source software and in teaching others how to be better programmers. I have a beautiful wife, 2 dogs (a dachshund and an olde-english bulldog) and a cat.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionThanks! Pin
Member 128328124-Nov-16 5:40
memberMember 128328124-Nov-16 5:40 
QuestionThank you! You saved me Pin
Member 922453021-Apr-15 3:53
memberMember 922453021-Apr-15 3:53 
GeneralMy vote of 5 Pin
Member 115052446-Mar-15 21:03
memberMember 115052446-Mar-15 21:03 
GeneralMy vote of 5 Pin
CHill6019-Feb-15 23:33
protectorCHill6019-Feb-15 23:33 
GeneralWonderful Explanation! Pin
Member 114355987-Feb-15 17:20
memberMember 114355987-Feb-15 17:20 
AnswerTHANKS MAN!!! Pin
PH Patrício10-Dec-14 3:20
memberPH Patrício10-Dec-14 3:20 
GeneralVote Of 5 Pin
diponsust22-Sep-14 9:24
memberdiponsust22-Sep-14 9:24 
GeneralWell done Pin
kevininstructor10-Jul-14 9:29
memberkevininstructor10-Jul-14 9:29 
QuestionI don't see the how the Twin Code column helps Pin
Developer_4603827-Aug-13 8:07
memberDeveloper_4603827-Aug-13 8:07 
AnswerRe: I don't see the how the Twin Code column helps Pin
Grossmeister14-Oct-13 4:36
memberGrossmeister14-Oct-13 4:36 
GeneralMy vote of 5 Pin
Lê Xuân Tiến17-Jun-13 8:54
memberLê Xuân Tiến17-Jun-13 8:54 
GeneralMy vote of 5 Pin
pablocci28-May-13 7:15
memberpablocci28-May-13 7:15 
GeneralMy vote of 5 Pin
nyaung15-Mar-13 20:27
membernyaung15-Mar-13 20:27 
GeneralMy vote of 5 Pin
wsc09184-Dec-12 22:32
memberwsc09184-Dec-12 22:32 
GeneralMy vote of 4 Pin
Ivan Dario Ospina17-Jul-12 12:13
memberIvan Dario Ospina17-Jul-12 12:13 
GeneralMy vote of 5 Pin
djj5514-Feb-12 8:21
memberdjj5514-Feb-12 8:21 
QuestionThere is a flow in this algorithm... Pin
georgejh4-Jan-12 1:02
membergeorgejh4-Jan-12 1:02 
AnswerRe: There is a flow in this algorithm... Pin
Dave C Andrews9-Jan-12 6:13
memberDave C Andrews9-Jan-12 6:13 
AnswerRe: There is a flow in this algorithm... Pin
stevehiner7-Nov-14 10:49
memberstevehiner7-Nov-14 10:49 
GeneralMy vote of 5 Pin
Jan Steyn2-Jan-12 0:22
memberJan Steyn2-Jan-12 0:22 
GeneralMy vote of 5 Pin
merlin9811-Jan-12 4:44
membermerlin9811-Jan-12 4:44 
SuggestionOff Topic - UserID Pin
thatraja31-Dec-11 6:28
mvpthatraja31-Dec-11 6:28 
GeneralRe: Off Topic - UserID Pin
Dave C Andrews3-Jan-12 9:16
memberDave C Andrews3-Jan-12 9:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170118.1 | Last Updated 31 Dec 2011
Article Copyright 2011 by Dave C Andrews
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid