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! Cheers!