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