ROWNUM

ROW_NUMBER

 SELECT ROW_NUMBER() OVER (ORDER BY Age),

       FirstName,

       Age

FROM Person

Row Number by Age    FirstName   Age
-------------------- ---------- ------
1                    Larry        5
2                    Doris        6
3                    George       6
4                    Mary         11
5                    Sherry       11
6                    Sam          17
7                    Ted          23
8                    Marty        23
9                    Sue          29
10                   Frank        38
11                   John         40

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age),

       FirstName,

       Age,

       Gender

FROM Person

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
3                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
5                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

RANK

SELECT RANK() OVER (ORDER BY Age),

       FirstName,

       Age

FROM Person

Rank by Age          FirstName  Age
-------------------- ---------- ------
1                    Larry      5
2                    Doris      6
2                    George     6
4                    Mary       11
4                    Sherry     11
6                    Sam        17
7                    Ted        23
7                    Marty      23
9                    Sue        29
10                   Frank      38
11                   John       40

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],

       FirstName,

       Age,

       Gender

FROM Person

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
2                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
4                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

DENSE_RANK

SELECT DENSE_RANK() OVER (ORDER BY Age),

       FirstName,

       Age

FROM Person

Dense Rank by Age    FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
2                    George     6
3                    Mary       11
3                    Sherry     11
4                    Sam        17
5                    Ted        23
5                    Marty      23
6                    Sue        29
7                    Frank      38
8                    John       40

NTILE

SELECT FirstName,

       Age,

       NTILE(3) OVER (ORDER BY Age) AS [Age Groups]

FROM Person

FirstName  Age         Age Groups
---------- ----------- --------------------
Larry      5           1
Doris      6           1
George     6           1
Mary       11          1
Sherry     11          2
Sam        17          2
Ted        23          2
Marty      23          2
Sue        29          3
Frank      38          3
John       40          3