>SQL SERVER: ROW_NUMBER () vs. DENSE_RANK ()


>

Recently, one of blog reader asked that how he can achieve following goals regarding row number for one of his sample table.
Team
Player
South Zone
SZ_Player1
South Zone
SZ_Player2
South Zone
SZ_Player3
North Zone
NZ_Player1
North Zone
NZ_Player2
National Team
NT_Player1
National Team
NT_Player2

·         A column which should return distinct sequential row number for all result rows
·         A column which should return distinct sequential row numbers for a specific partition i.e.  Each team member sequential number
·         A column which should return distinct sequential number for each team

And results must be like this
Team
Player
Record Number
Team Member Number
Team Number
South Zone
SZ_Player1
1
1
1
South Zone
SZ_Player2
2
2
1
South Zone
SZ_Player3
3
3
1
North Zone
NZ_Player1
4
1
2
North Zone
NZ_Player2
5
2
2
National Team
NT_Player1
6
1
3
National Team
NT_Player2
7
2
3
Solution:
ROW_NUMBER() will be used to achieve first two goals as ROW_NUMBER() return sequential row number within a partition of result set. And for last column we will use DENSE_RANK(), which will return rank of rows within the partition of a result set.
–Create temporary table for query testing
CREATE TABLE #TeamPlayer
    (
      Team VARCHAR(25),
      PlayerName VARCHAR(25)
    )
GO
— Insert temporary records
INSERT  INTO #TeamPlayer ( Team, PlayerName )
        SELECT  ‘South Zone’,
                ‘SZ_Player1’
        UNION ALL
        SELECT  ‘South Zone’,
                ‘SZ_Player2’
        UNION ALL
        SELECT  ‘South Zone’,
                ‘SZ_Player3’
        UNION ALL
        SELECT  ‘North Zone’,
                ‘NS_Player1’
        UNION ALL
        SELECT  ‘North Zone’,
                ‘NS_Player2’
        UNION ALL
        SELECT  ‘National Team’,
                ‘NT_Player1’
        UNION ALL
        SELECT  ‘National Team’,
                ‘NT_Player2’
GO
— Query to get results

SELECT  Team,
        PlayerName,
        DENSE_RANK() OVER ( ORDER BY Team ) AS TeamNumber,
        ROW_NUMBER() OVER ( PARTITION BY Team ORDER BY PlayerName ) AS TeamMemberNumber,
        ROW_NUMBER() OVER ( ORDER BY PlayerName ) AS RecordNumber
FROM    #TeamPlayer

GO
DROP TABLE #TeamPlayer
Advertisements

Posted on January 10, 2011, in TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: