Archive

Posts Tagged ‘rank’

SQL Functions – ROW_NUMBER()

01.07.2011 No comments

ROW_NUMBER() can be used to generate a sequential number for each row in the result set. Unlike RANK() and DENSE_RANK(), ROW_NUMBER() in case of ties it does not generate same number, it simply ignores the tie and generates sequential numbers for each of the tied rows.

For example,

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      Products.ProductID, Products.Name,

            Products.Category, Products.Price,

            ROW_NUMBER() OVER (ORDER BY [Price])

            AS [RowNumber]

FROM

(

      SELECT 804 AS [ProductID],

      'HL Fork' AS [Name], 'Accessories' AS [Category],

      229.49 AS [Price] UNION ALL

      SELECT 871, 'Mountain Bottle Cage', 'Accessories', 9.99    UNION ALL

      SELECT 921, 'Mountain Tire Tube',   'Parts',       24.99   UNION ALL

      SELECT 845, 'Mountain Pump',        'Parts',       24.99   UNION ALL

      SELECT 980, 'Mountain-400-W Silver','Accessories', 769.49  UNION ALL

      SELECT 988, 'Mountain-500 Silver',  'Parts',       564.99  UNION ALL

      SELECT 793, 'Road-250 Black',       'Accessories', 2443.35 UNION ALL

      SELECT 762, 'Road-650 Red',         'Parts',       782.99  UNION ALL

      SELECT 923, 'Touring Tire Tube',    'Accessories', 4.99    UNION ALL

      SELECT 966, 'Touring-1000 Blue',    'Parts',       2384.07

) Products

ROW_NUMBER() Result Set:

ProductID   Name                  Category    Price         RowNumber

———– ——————— ———– ————- ————

923         Touring Tire Tube     Accessories 4.99          1

871         Mountain Bottle Cage  Accessories 9.99          2

845         Mountain Pump         Parts       24.99         3

921         Mountain Tire Tube    Parts       24.99         4

804         HL Fork               Accessories 229.49        5

988         Mountain-500 Silver   Parts       564.99        6

980         Mountain-400-W Silver Accessories 769.49        7

762         Road-650 Red          Parts       782.99        8

966         Touring-1000 Blue     Parts       2384.07       9

793         Road-250 Black        Accessories 2443.35       10

 

(10 row(s) affected)

If we had used RANK() function, it would have generated the result as below:

……………

871         Mountain Bottle Cage  Accessories 9.99            2

845         Mountain Pump         Parts       24.99           3

921         Mountain Tire Tube    Parts       24.99           3

804         HL Fork               Accessories 229.49          5
……………

and DENSE_RANK() would have generated 4 for 'HL Fork'.

 

Using ROW_NUMBER with <PARTITION BY>:

By using PARTITION BY clause with ROW_NUMBER() the result set can be divided into number of result sets based on the PARTITION BY column, and to each result set ROW_NUMBER() function will be applied independently.

We can apply PARTITION BY clause to above data to partition the result sets by Product Category.

The result set would contain:

            Partition By Category

10 Rows ->  Accessories (5 Rows) -> Apply ROW_NUMBER() -> 1, 2, 3, 4, 5

            Parts (5 Rows)       -> Apply ROW_NUMBER() -> 1, 2, 3, 4, 5

SELECT      Products.ProductID, Products.Name,

            Products.Category, Products.Price,

            ROW_NUMBER() OVER (PARTITION BY [Category] ORDER BY [Price])

            AS [RowNumber]

FROM

(

      SELECT 804 AS [ProductID],

      'HL Fork' AS [Name], 'Accessories' AS [Category],

      229.49 AS [Price] UNION ALL

      SELECT 871, 'Mountain Bottle Cage', 'Accessories', 9.99    UNION ALL

      SELECT 921, 'Mountain Tire Tube',   'Parts',       24.99   UNION ALL

      SELECT 845, 'Mountain Pump',        'Parts',       24.99   UNION ALL

      SELECT 980, 'Mountain-400-W Silver','Accessories', 769.49  UNION ALL

      SELECT 988, 'Mountain-500 Silver',  'Parts',       564.99  UNION ALL

      SELECT 793, 'Road-250 Black',       'Accessories', 2443.35 UNION ALL

      SELECT 762, 'Road-650 Red',         'Parts',       782.99  UNION ALL

      SELECT 923, 'Touring Tire Tube',    'Accessories', 4.99    UNION ALL

      SELECT 966, 'Touring-1000 Blue',    'Parts',       2384.07

) Products

Result Set:

ProductID   Name                  Category    Price         RowNumber

———– ——————— ———– ———–   ————

923         Touring Tire Tube     Accessories 4.99          1

871         Mountain Bottle Cage  Accessories 9.99          2

804         HL Fork               Accessories 229.49        3

980         Mountain-400-W Silver Accessories 769.49        4

793         Road-250 Black        Accessories 2443.35       5

845         Mountain Pump         Parts       24.99         1

921         Mountain Tire Tube    Parts       24.99         2

988         Mountain-500 Silver   Parts       564.99        3

762         Road-650 Red          Parts       782.99        4

966         Touring-1000 Blue     Parts       2384.07       5

 

(10 row(s) affected)

Hope This Helps!

Vishal

SQL Functions – RANK() & DENSE_RANK()

29.06.2011 No comments

The RANK() functions ranks each row of a result set. It can also be used to partition the data for ranking. It takes two arguments, PARTITION BY clause and ORDER BY clause. As the name suggests PARTITION BY clause is used to partition the result set into chunks, and ORDER BY defines the order of the rows.

For example,

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE   AdventureWorks2008R2

 

SELECT      TOP (10)

            OrganizationLevel, JobTitle, LoginID, HireDate,

            RANK() OVER (ORDER BY HireDate ASC) AS 'Rank'

FROM        HumanResources.Employee

Result Set:

image

In the above example, RANK() function is used to rank each row based on HireDate, no partitioning is used. Now if we need to rank all rows for each OrganizationLevel individually, we need to add PARTITION BY clause to RANK() as below:

SELECT      TOP (10)

            OrganizationLevel, JobTitle, LoginID, HireDate,

            RANK() OVER (PARTITION BY OrganizationLevel

                        ORDER BY HireDate ASC) AS 'Rank'

FROM  HumanResources.Employee

Result Set:

image

In this case, for OrganizationLevel = 1, the ranking again starts from 1, same way for OrganizationLevel = 2.

However, RANK() functions does not always assign consecutive numbers to rows, if you look at the first example, there is a tie while assigning rank 8, in this case both rows are assigned rank 8, and the next rank assigned is 10.

In cases where you need consecutive numbers to be assigned, you can use DENSE_RANK():

SELECT      TOP (10)

            OrganizationLevel, JobTitle, LoginID, HireDate,

            DENSE_RANK() OVER (ORDER BY HireDate ASC) AS 'Rank'

FROM  HumanResources.Employee

Result Set:

image

Hope This Helps!

Vishal