Numbering record groups - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Numbering record groups

Author

Topic

Mungbeans
Starting Member

7 Posts

Posted - 2011-01-29 : 16:10:02

Is there a way to number groups of records sequentially?

I can use row_number() to number records within a group, but am having trouble working out how to the the groups of records their own sequential numbering. Here is an example:

GroupNo RecordNo Data
1 1 First Group A
1 2 First Group B
1 3 First Group C
2 1 Second Group A
2 2 Second Group B
3 1 Third Group A
3 2 Third Group B
3 3 Third Group D

I know how to get the RecordNo using Row_number(), but I am having trouble numbering the groups themselves : eg: GroupNo in the example above. Is there a way to do this without multiple temp tables?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-29 : 16:24:26

If you "know" about row_number() function, then you must have read about PARTITION BY part of the windowed function?
And you can use DENSE_RANK too, read about it in Books Online.

N 56°04'39.26"
E 12°55'05.63"

Mungbeans
Starting Member

7 Posts

Posted - 2011-01-29 : 16:37:40

I haven't heard of DENSE_RANK - I'm off to read about it now. Sometimes until you have seen these functions in action its hard to know what they are for!

I'll let you know if it works.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-29 : 17:25:20

For the page of ROW_NUMBER(), look at the very bottom as there are related topics.

N 56°04'39.26"
E 12°55'05.63"

Mungbeans
Starting Member

7 Posts

Posted - 2011-01-29 : 17:40:58

Unfortunately DENSE_RANK doesn't seem to be what I am after:

Create table MyGroups (
GroupName varchar(64),
PersonName varchar(64),
PersonID int,
Joined datetime
)

Insert into MyGroups values ('Blue Group', 'Sally', 52, '20080102')
Insert into MyGroups values ('Blue Group', 'Tom', 2, '20060102')
Insert into MyGroups values ('Blue Group', 'Yvonne', 100, '20100102')
Insert into MyGroups values ('Red Group', 'Tilley', 22, '20060102')
Insert into MyGroups values ('Red Group', 'Jan', 55, '20080102')
Insert into MyGroups values ('Yellow Group', 'John', 1, '20051017')
Insert into MyGroups values ('Yellow Group', 'Irene', 8, '20060915')
Insert into MyGroups values ('Yellow Group', 'Shirley', 52, '20060102')

select
GroupNumber= DENSE_RANK() OVER (PARTITION BY GroupName ORDER BY GroupName),
NoMembers= count(PersonID) over (partition by GroupName),
GroupName,
PersonName,
PersonID,
Seniority= row_Number() over (partition by GroupName order by joined),
Joined
from MyGroups

This gives every Group a number of 1:

Group NoMembers GroupName PersonName PersonID Seniority Joined
Number
1 3 Blue Group Tom 2 1 2006-01-02 00:00:00.000
1 3 Blue Group Sally 52 2 2008-01-02 00:00:00.000
1 3 Blue Group Yvonne 100 3 2010-01-02 00:00:00.000
1 2 Red Group Tilley 22 1 2006-01-02 00:00:00.000
1 2 Red Group Jan 55 2 2008-01-02 00:00:00.000
1 3 Yellow Group John 1 1 2005-10-17 00:00:00.000
1 3 Yellow Group Shirley 52 2 2006-01-02 00:00:00.000
1 3 Yellow Group Irene 8 3 2006-09-15 00:00:00.000

Whereas what I am after is this:

Group NoMembers GroupName PersonName PersonID Seniority Joined
Number
1 3 Blue Group Tom 2 1 2006-01-02 00:00:00.000
1 3 Blue Group Sally 52 2 2008-01-02 00:00:00.000
1 3 Blue Group Yvonne 100 3 2010-01-02 00:00:00.000
2 2 Red Group Tilley 22 1 2006-01-02 00:00:00.000
2 2 Red Group Jan 55 2 2008-01-02 00:00:00.000
3 3 Yellow Group John 1 1 2005-10-17 00:00:00.000
3 3 Yellow Group Shirley 52 2 2006-01-02 00:00:00.000
3 3 Yellow Group Irene 8 3 2006-09-15 00:00:00.000

Mungbeans
Starting Member

7 Posts

Posted - 2011-01-29 : 18:03:23

OK - I've got it! The grouping happens in the Order by Clause - so I must include all the records in a fictitious partition (1) and then let the order by do its magic:

select
GroupNo= dense_rank() over (partition by 1 order by my.groupname),
RN= row_number() over (partition by 1 order by my.groupname, joined),
NoMembers= count(PersonID) over (partition by my.GroupName),
my.GroupName,
PersonName,
PersonID,
Seniority= DENSE_RANK() over (partition by my.GroupName order by joined),
Joined
from MyGroups my

GroupNo RN NoMembers GroupName PersonName PersonID Seniority Joined
1 1 3 Blue Group Tom 2 1 2006-01-02 00:00:00.000
1 2 3 Blue Group Sally 52 2 2008-01-02 00:00:00.000
1 3 3 Blue Group Yvonne 100 3 2010-01-02 00:00:00.000
2 4 2 Red Group Tilley 22 1 2006-01-02 00:00:00.000
2 5 2 Red Group Jan 55 2 2008-01-02 00:00:00.000
3 6 3 Yellow Group John 1 1 2005-10-17 00:00:00.000
3 7 3 Yellow Group Irene 8 1 2005-10-17 00:00:00.000
3 8 3 Yellow Group Shirley 52 2 2006-01-02 00:00:00.000

Very nice!

Mungbeans
Starting Member

7 Posts

Posted - 2011-01-29 : 18:44:16

Just discovered I can leave out the 'Partition by' part:

select
GroupNo= dense_rank() over (order by my.groupname),
RN= row_number() over (order by my.groupname, joined),
NoMembers= count(PersonID) over (partition by my.GroupName),
my.GroupName,
PersonName,
PersonID,
Seniority= DENSE_RANK() over (partition by my.GroupName order by joined),
Joined
from MyGroups my

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources