Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > SQL Functions – ROW_NUMBER()

SQL Functions – ROW_NUMBER()

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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: