Exploring ROW_NUMBER function in SQL Server 2005

Some of the SQL Server 2005 new features are the Ranking functions which are used to analyse data. Among these functions we have, for instance, ROW_NUMBER which helps to enumerate every row of a result set in a simpler way. Before showing the illustration of it, I will share the syntax:

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )
where:

• partition_by_clause: Divide the result set generated by FROM clause into partitions on which ROW_NUMBER will work.
• order_by_clause: Determine the order for ROW_NUMBER.

Now the illustration: we are going to enumerate the details of the sales based on the price.

SELECT ROW_NUMBER() OVER( ORDER BY UnitPrice DESC ) AS Item, 
SaleOrderID, ProductID, Quantity,UnitPrice 
FROM OrderDetail 

Item    SaleOrderID     ProductID   Quantity    UnitPrice
----    ------------    ---------   --------    -----------
1        00000015       FX8S           1        7621.71
2        00000016       FX8E           1        3601.77
3        00000016       FX5P           1        2207.87
4        00000014       FX5G           1        6507.82
5        00000018       FX21           1        631.62
6        00000019       FX19           1        751.62

Another example of it would be using PARTITION clause for partitions result sets by ProductGroupID and then ROW_NUMBER will be applied based on ProductStock. Therefore, we are writing a query like this:

SELECT ROW_NUMBER() OVER(PARTITION BY ProductGroupID ORDER BY ProductStock ASC) AS Item, 
ProductID, ProductDesc, ProductGroupID, ProductStock 
FROM Products

Item  ProductID    ProductDesc                       ProductGroupID    ProductStock

----  ---------    ------------------------------    --------------    -----------
1     DS02         MAINBOARD 845 PEMYL               G003               -1
2     DS06         MAINBOARD 850 EV2                 G003               10
1     DS07         TECLADO 104 MULTIMEDIA32          G004               20
1     DS12         SWITCH 5 PORT 10/100, 220V        G006               26
1     DS45         MUEBLE DE OFICINA                 G007               34
1     DS76         DISCO DURO                        G008               20
1     DS07         IMPRESORA LASER                   G009               10
1     DS45         MEMORIA STICK                     G010               2
2     DS29         MEMORIA VIDEO                     G010               5
1     DS08         TINTA CANON BCI24 NEGRO           G011               12
1     DS41         WEB CAM ALTIOR MODELO B02         G012               0
1     DS74         MONITOR DE 15                     G016               6
1     DS83         IMPRESORA MATRICIAL DFX5001       G018               12
2     DS84         IMPRESORA MATRICIAL DFX8502       G018               17
3     DS21         IMPRESORA MATRICIAL FX21944       G018               48

(15 row(s) affected)

As I mentioned before, this functionality is helpful, simple and easy to use it, so it does not worth a major explanation. That is all for now. Thanks for reading. Stay tuned.