How to rank rows by grouping without using cursors with SQL Server

On occasion, you need to group and rank rows in consecutive order without any gap. The usual way is to create a temporary table and to use cursors in Transact-SQL to increment and reset the counters.

SQL Server 2005 introduced the function DENSE_RANK that does it all.

Data Used

  1. USE sql911;
  2. go
  3.  
  4. SELECT EMPNO AS 'Emp #',
  5.        FIRSTNME AS 'First Name',
  6.        LASTNAME AS 'Last Name',
  7.        isnull(WORKDEPT,'**') AS 'Dept',
  8.        isnull(PHONENO,'**') AS 'Local', 
  9.        HIREDATE AS 'Hired',
  10.        JOB AS 'Position'
  11. FROM emps
  12. ORDER BY lastname, firstnme;
  13. go
Emp #  First Name   Last Name       Dept Local Hired                   Position
------ ------------ --------------- ---- ----- ----------------------- --------
000150 BRUCE        ADAMSON         **   **    1987-02-12 00:00:00.000 DESIGNER
000200 DAVID        BROWN           D11  4501  1981-03-03 00:00:00.000 DESIGNER
000050 JOHN         GEYER           E01  6789  1964-08-17 00:00:00.000 MANAGER 
000340 JASON        GOUNOT          E21  5698  1962-05-05 00:00:00.000 FIELDREP
000010 CHRISTINE    HAAS            A00  3978  1980-01-01 00:00:00.000 PRES    
000090 EILEEN#      HENDERSON       E11  5498  1985-08-15 00:00:00.000 MANAGER 
000230 JAMES        JEFFERSON       D21  2094  1981-11-21 00:00:00.000 CLERK   
000260 SYBIL\       JOHNSON         D21  8953  1990-09-11 00:00:00.000 CLERK   
000210 WILLIAM*     JONES           D11  0942  1994-04-11 00:00:00.000 DESIGNER
000030 SALLY!       KWAN            C01  4738  1990-04-05 00:00:00.000 MANAGER 
000330 WING         LEE             E21  2103  1991-02-23 00:00:00.000 FIELDREP
000110 VINCENZO.    LUCCHESSI       A00  3490  1973-05-16 00:00:00.000 SALESREP
000220 JENNIFER]    LUTZ            **   **    1983-08-29 00:00:00.000 DESIGNER
000240 SALVATORE[   MARINO          D21  3780  1994-12-05 00:00:00.000 CLERK   
000320 RAMLAL       MEHTA           **   9990  1980-07-07 00:00:00.000 FIELDREP
000140 HEATHER      NICHOLLS        C01  1793  1991-12-15 00:00:00.000 ANALYST 
000120 SEAN         O CONNELL       A00  2167  1978-12-05 00:00:00.000 CLERK   
000290 JOHN         PARKER          E11  4502  1995-05-30 00:00:00.000 OPERATOR
000270 MARIA        PEREZ           D21  9001  1995-09-30 00:00:00.000 CLERK   
000160 ELIZABETH    PIANKA          D11  3782  1992-10-11 00:00:00.000 DESIGNER
000070 EVA          PULASKI         D21  7831  1995-09-30 00:00:00.000 MANAGER 
000130 DOLORES,     QUINTANA        C01  4578  1986-07-28 00:00:00.000 ANALYST 
000280 ETHEL/       SCHNEIDER       E11  8997  1982-03-24 00:00:00.000 OPERATOR
000180 MARILYN      SCOUTTEN        D11  1682  1988-07-07 00:00:00.000 DESIGNER
000310 MAUDE        SETRIGHT        E11  3332  1979-09-12 00:00:00.000 OPERATOR
000250 DANIEL       SMITH           D21  0961  1984-10-30 00:00:00.000 CLERK   
000300 PHILIP       SMITH           E11  2095  1987-06-19 00:00:00.000 OPERATOR
000100 THEODORE     SPENSER         **   **    1995-06-19 00:00:00.000 MANAGER 
000060 IRVING@      STERN           D11  6423  1988-09-14 00:00:00.000 MANAGER 
000020 MICHAEL      THOMPSON        **   3476  1988-10-10 00:00:00.000 MANAGER 
000190 JAMES&       WALKER          D11  2986  1989-07-26 00:00:00.000 DESIGNER
000170 MASATOSHI%   YOSHIMURA       D11  2890  1993-09-15 00:00:00.000 DESIGNER

(32 row(s) affected)

Ranking

  1. USE sql911;
  2. go
  3.  
  4. SELECT CAST(ROW_NUMBER() OVER (ORDER BY lastname, firstnme) AS VARCHAR(8)) AS 'Row #',
  5.        CAST(dense_rank() OVER (ORDER BY workdept) AS VARCHAR(8)) AS '# Dept',
  6.        EMPNO AS 'Emp#',
  7.        FIRSTNME AS 'First Name',
  8.        LASTNAME AS 'Last Name',
  9.        isnull(WORKDEPT,'**') AS 'Dept',
  10.        isnull(PHONENO,'**') AS 'Local', 
  11.        HIREDATE AS 'Hired',
  12.        JOB AS 'Position'
  13. FROM emps
  14. ORDER BY workdept, lastname, firstnme;
  15. go
Row #    # Dept   Emp#   First Name   Last Name       Dept Local Hired                   Position
-------- -------- ------ ------------ --------------- ---- ----- ----------------------- --------
1        1        000150 BRUCE        ADAMSON         **   **    1987-02-12 00:00:00.000 DESIGNER
13       1        000220 JENNIFER]    LUTZ            **   **    1983-08-29 00:00:00.000 DESIGNER
15       1        000320 RAMLAL       MEHTA           **   9990  1980-07-07 00:00:00.000 FIELDREP
28       1        000100 THEODORE     SPENSER         **   **    1995-06-19 00:00:00.000 MANAGER 
30       1        000020 MICHAEL      THOMPSON        **   3476  1988-10-10 00:00:00.000 MANAGER 
5        2        000010 CHRISTINE    HAAS            A00  3978  1980-01-01 00:00:00.000 PRES    
12       2        000110 VINCENZO.    LUCCHESSI       A00  3490  1973-05-16 00:00:00.000 SALESREP
17       2        000120 SEAN         O CONNELL       A00  2167  1978-12-05 00:00:00.000 CLERK   
10       3        000030 SALLY!       KWAN            C01  4738  1990-04-05 00:00:00.000 MANAGER 
16       3        000140 HEATHER      NICHOLLS        C01  1793  1991-12-15 00:00:00.000 ANALYST 
22       3        000130 DOLORES,     QUINTANA        C01  4578  1986-07-28 00:00:00.000 ANALYST 
2        4        000200 DAVID        BROWN           D11  4501  1981-03-03 00:00:00.000 DESIGNER
9        4        000210 WILLIAM*     JONES           D11  0942  1994-04-11 00:00:00.000 DESIGNER
20       4        000160 ELIZABETH    PIANKA          D11  3782  1992-10-11 00:00:00.000 DESIGNER
24       4        000180 MARILYN      SCOUTTEN        D11  1682  1988-07-07 00:00:00.000 DESIGNER
29       4        000060 IRVING@      STERN           D11  6423  1988-09-14 00:00:00.000 MANAGER 
31       4        000190 JAMES&       WALKER          D11  2986  1989-07-26 00:00:00.000 DESIGNER
32       4        000170 MASATOSHI%   YOSHIMURA       D11  2890  1993-09-15 00:00:00.000 DESIGNER
7        5        000230 JAMES        JEFFERSON       D21  2094  1981-11-21 00:00:00.000 CLERK   
8        5        000260 SYBIL\       JOHNSON         D21  8953  1990-09-11 00:00:00.000 CLERK   
14       5        000240 SALVATORE[   MARINO          D21  3780  1994-12-05 00:00:00.000 CLERK   
19       5        000270 MARIA        PEREZ           D21  9001  1995-09-30 00:00:00.000 CLERK   
21       5        000070 EVA          PULASKI         D21  7831  1995-09-30 00:00:00.000 MANAGER 
26       5        000250 DANIEL       SMITH           D21  0961  1984-10-30 00:00:00.000 CLERK   
3        6        000050 JOHN         GEYER           E01  6789  1964-08-17 00:00:00.000 MANAGER 
6        7        000090 EILEEN#      HENDERSON       E11  5498  1985-08-15 00:00:00.000 MANAGER 
18       7        000290 JOHN         PARKER          E11  4502  1995-05-30 00:00:00.000 OPERATOR
23       7        000280 ETHEL/       SCHNEIDER       E11  8997  1982-03-24 00:00:00.000 OPERATOR
25       7        000310 MAUDE        SETRIGHT        E11  3332  1979-09-12 00:00:00.000 OPERATOR
27       7        000300 PHILIP       SMITH           E11  2095  1987-06-19 00:00:00.000 OPERATOR
4        8        000340 JASON        GOUNOT          E21  5698  1962-05-05 00:00:00.000 FIELDREP
11       8        000330 WING         LEE             E21  2103  1991-02-23 00:00:00.000 FIELDREP

(32 row(s) affected)
  1. The DENSE_RANK() function does not have any gap, all the numbers are always consecutive.
  2. If 2 rows have the same value of the OVER, they will have the same DENSE_RANK(), the following row will have DENSE_RANK() + 1* and NOT DENSE_RANK() + Number of duplicates.
  3. The OVER order does not have to be the same as the order of the ORDER BY clause. The DENSE_RANK, it will generate it's own sort. You can see the showplan for the details.
  4. The OVER order should be the same as the order of the ORDER BY clause. It's lot more efficient, especially for large tables.
  5. The OVER order should be covered by some index, especially if the report is a large one and used often.
  6. Note that the order for the ROW_NUMBER does not have to match either the ORDER BY clause or the DENSE_RANK, [in this case: an alphabetical sort for the employee names.]

Showplan

  1. SET showplan_text ON;
  2. go
  3.  
  4. SELECT CAST(ROW_NUMBER() OVER (ORDER BY lastname, firstnme) AS VARCHAR(8)) AS 'Row #',
  5.        CAST(dense_rank() OVER (ORDER BY workdept) AS VARCHAR(8)) AS '# Dept',
  6.        EMPNO AS 'Emp#',
  7.        FIRSTNME AS 'First Name',
  8.        LASTNAME AS 'Last Name',
  9.        isnull(WORKDEPT,'**') AS 'Dept',
  10.        isnull(PHONENO,'**') AS 'Local', 
  11.        HIREDATE AS 'Hired',
  12.        JOB AS 'Position'
  13. FROM emps
  14. ORDER BY workdept, lastname, firstnme;
  15. go
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select cast(ROW_NUMBER() OVER (ORDER BY lastname, firstnme) as varchar(8)) AS 'Row #',
       cast(dense_rank() over (order by workdept) as varchar(8)) as '# Dept',
       EMPNO as 'Emp#', 
       FIRSTNME as 'First Name', 
       LASTNAME as 'Last N

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(ORDER BY:([sql911].[dbo].[Emps].[WORKDEPT] ASC, [sql911].[dbo].[Emps].[LASTNAME] ASC, [sql911].[dbo].[Emps].[FIRSTNME] ASC))
       |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(varchar(8),[Expr1004],0), [Expr1007]=CONVERT(varchar(8),[Expr1005],0), [Expr1008]=isnull([sql911].[dbo].[Emps].[WORKDEPT],'** '), [Expr1009]=isnull([sql911].[dbo].[Emps].[PHONENO],'**  ')))
            |--Sequence Project(DEFINE:([Expr1005]=dense_rank))
                 |--Segment
                      |--Segment
                           |--Sort(ORDER BY:([sql911].[dbo].[Emps].[WORKDEPT] ASC))
                                |--Sequence Project(DEFINE:([Expr1004]=row_number))
                                     |--Compute Scalar(DEFINE:([Expr1011]=(1)))
                                          |--Segment
                                               |--Sort(ORDER BY:([sql911].[dbo].[Emps].[LASTNAME] ASC, [sql911].[dbo].[Emps].[FIRSTNME] ASC))
                                                    |--Table Scan(OBJECT:([sql911].[dbo].[Emps]))

(11 row(s) affected)
  1. The graphical vue of the SHOWPLAN is much more detailed, you can actually see the IO cost, the CPU cost, the number of rows...
  2. The plan is evaluated from the last item: Table Scan(OBJECT:([sql911].[dbo].[Emps])) to the first item: Sort(ORDER BY:([sql911].[dbo].[Emps].[WORKDEPT] ASC, [sql911].[dbo].[Emps].[LASTNAME] ASC, [sql911].[dbo].[Emps].[FIRSTNME] ASC))
  3. You can clearly see how SQL Server does all the sorts at each stage.