ROW_NUMBER、RANK、DENSE_RANK & NTILE

Create Test Table

IF object_id('Scoring') IS NOT NULL DROP TABLE Scoring
GO
CREATE TABLE Scoring
(
    Name        VARCHAR(10) NOT NULL PRIMARY KEY,
    Team        VARCHAR(10) NOT NULL,
    Score       INT         NOT NULL
)
GO
INSERT INTO Scoring
VALUES    ('Dan',     'PCO',    3),
          ('Ron',     'PCC',    9),
          ('Kathy',   'PCO',    8),
          ('Suzanne', 'ECO',    9),
          ('Joe',     'PCC',    6),
          ('Robert',  'PCC',    6),
          ('Mike',    'ECO',    8),
          ('Michele', 'PCO',    8),
          ('Jessica', 'PCC',    9),
          ('Brian',   'PCO',    7),
          ('Kevin',   'ECO',    7)

ROW_NUMBER

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

Example

SELECT ROW_NUMBER() OVER(ORDER BY Name) as ID ,Name,Team,Score FROM Scoring

ID                   Name       Team       Score
-------------------- ---------- ---------- -----------
1                    Brian      PCO        7
2                    Dan        PCO        3
3                    Jessica    PCC        9
4                    Joe        PCC        6
5                    Kathy      PCO        8
6                    Kevin      ECO        7
7                    Michele    PCO        8
8                    Mike       ECO        8
9                    Robert     PCC        6
10                   Ron        PCC        9
11                   Suzanne    ECO        9

SELECT Team,ROW_NUMBER() OVER(PARTITION BY Team ORDER BY Score DESC) as SubID,Name,Score FROM Scoring

Team       SubID                Name       Score
---------- -------------------- ---------- -----------
ECO        1                    Suzanne    9
ECO        2                    Mike       8
ECO        3                    Kevin      7
PCC        1                    Jessica    9
PCC        2                    Ron        9
PCC        3                    Joe        6
PCC        4                    Robert     6
PCO        1                    Michele    8
PCO        2                    Kathy      8
PCO        3                    Brian      7
PCO        4                    Dan        3

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

Syntax

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

Example

1:  SELECT Name,Team,Score,RANK() OVER(ORDER BY Score DESC) AS RankNumber FROM Scoring

Name       Team       Score       RankNumber
---------- ---------- ----------- --------------------
Jessica    PCC        9           1
Ron        PCC        9           1
Suzanne    ECO        9           1
Kathy      PCO        8           4
Michele    PCO        8           4
Mike       ECO        8           4
Kevin      ECO        7           7
Brian      PCO        7           7
Joe        PCC        6           9
Robert     PCC        6           9
Dan        PCO        3           11

SELECT Name,Team,Score,RANK() OVER(PARTITION BY Team ORDER BY Score DESC) AS SubRankNumber FROM Scoring

Name       Team       Score       SubDRankNumber
---------- ---------- ----------- --------------------
Suzanne    ECO        9           1
Mike       ECO        8           2
Kevin      ECO        7           3
Jessica    PCC        9           1
Ron        PCC        9           1
Joe        PCC        6           2
Robert     PCC        6           2
Michele    PCO        8           1
Kathy      PCO        8           1
Brian      PCO        7           2
Dan        PCO        3           3

DENSE_RANK

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Syntax

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Example

PRE>1: SELECT Name,Team,Score,DENSE_RANK() OVER(ORDER BY Score DESC) AS DRankNumber FROM Scoring

Name       Team       Score       DRankNumber
---------- ---------- ----------- --------------------
Jessica    PCC        9           1
Ron        PCC        9           1
Suzanne    ECO        9           1
Kathy      PCO        8           2
Michele    PCO        8           2
Mike       ECO        8           2
Kevin      ECO        7           3
Brian      PCO        7           3
Joe        PCC        6           4
Robert     PCC        6           4
Dan        PCO        3           5

SELECT Name,Team,Score,DENSE_RANK() OVER(PARTITION BY Team ORDER BY Score DESC) AS SubDRankNumber FROM Scoring

Name       Team       Score       SubDRankNumber
---------- ---------- ----------- --------------------
Suzanne    ECO        9           1
Mike       ECO        8           2
Kevin      ECO        7           3
Jessica    PCC        9           1
Ron        PCC        9           1
Joe        PCC        6           2
Robert     PCC        6           2
Michele    PCO        8           1
Kathy      PCO        8           1
Brian      PCO        7           2
Dan        PCO        3           3

<

NTILE

Distributes the row in an ordered partition into a specified number of groups. The groups are numbered,starting at one. For each row, NTILE returns the number of the group to which the row belongs.

Syntax

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Example

SELECT Name,Team,Score,NTILE(3) OVER(ORDER BY Score DESC) AS NtileNumber FROMScoring

Name       Team       Score       NtileNumber
---------- ---------- ----------- --------------------
Jessica    PCC        9           1
Ron        PCC        9           1
Suzanne    ECO        9           1
Kathy      PCO        8           1
Michele    PCO        8           2
Mike       ECO        8           2
Kevin      ECO        7           2
Brian      PCO        7           2
Joe        PCC        6           3
Robert     PCC        6           3
Dan        PCO        3           3

   1:  SELECT Name,Team,Score,NTILE(3) OVER(PARTITION BY Team ORDER BY Score DESC) AS SubNtileNumber FROM Scoring

Name       Team       Score       SubNtileNumber
---------- ---------- ----------- --------------------
Suzanne    ECO        9           1
Mike       ECO        8           2
Kevin      ECO        7           3
Jessica    PCC        9           1
Ron        PCC        9           1
Joe        PCC        6           2
Robert     PCC        6           3
Michele    PCO        8           1
Kathy      PCO        8           1
Brian      PCO        7           2
Dan        PCO        3           3

SELECT Name,Team,Score,
CASE NTILE(3) OVER(ORDER BY Score DESC)
    WHEN 1 THEN 'High'
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'Low'
END    AS ScoreCategory
FROM Scoring

Name       Team       Score       ScoreCategory
---------- ---------- ----------- -------------
Jessica    PCC        9           High
Ron        PCC        9           High
Suzanne    ECO        9           High
Kathy      PCO        8           High
Michele    PCO        8           Medium
Mike       ECO        8           Medium
Kevin      ECO        7           Medium
Brian      PCO        7           Medium
Joe        PCC        6           Low
Robert     PCC        6           Low
Dan        PCO        3           Low