After reading the excellent Web-exclusive article “Calculating Row Numbers in SQL Server 2005,” April 2004, InstantDoc ID 42302, I started doing some tests and found one case in which I couldn’t think a way to use the ROW_NUMBER() function without using subqueries. Using the Northwind database, I wanted to write a query to create a new identifier for each order and another new identifier for each product that belongs to an order. The result should be similar to





\[Order Details\] in Northwind      Expected Query Output                              10248   11   14.0000   12         1   1   14.0000   12                              10248   42    9.8000   10         1   2    9.8000   10                              10248   72   34.8000    5         1   3   34.8000    5                              10249   14   18.6000    9         2   1   18.6000    9                              10249   51   42.4000   40         2   2   42.4000   40                              10250   41    7.7000   10         3   1   7.7000    10                              10250   51   42.4000   35         3   2   42.4000   35                              10250   65   16.8000   15         3   3   16.8000   15

Without the ROW_NUMBER() function, I could get the expected result with this query:

SELECT                                 (SELECT count(*) FROM orders as o2                                   WHERE o2.OrderID                               FROM Orders AS O                               inner join \[order details\] AS OD                               ON O.OrderID = od.OrderID                               inner join \[products\] as p                               on p.productID = od.productiD 

With the ROW_NUMBER() function, I found the result with this query:

SELECT                                 NewOrderNumber,                                ROW_NUMBER() OVER (PARTITION BY O.ORDERID ORDER BY OD.ProductID) as                               ProductNumber,                                OD.OrderID,                                 OrderDate,                                 OD.ProductId,                                 ProductName,                                 Quantity                              FROM                                 (SELECT ROW_Number() OVER (ORDER BY OrderID) as NewOrderNumber, *                                 FROM Orders) AS O                               INNER JOIN \[Order Details\] AS OD                              ON O.OrderID = OD.oRDERid                              inner join \[Products\] as p                              ON p.productID = od.productiD

However, I couldn’t think of a way to resolve this query without using subqueries. I was out of ideas, so I asked Itzik Ben-Gan, who wrote “Calculating Row Numbers in SQL Server 2005.” Because the order IDs aren’t unique in the result of the join, he suggested that I simply use the DENSE_RANK() function instead of the ROW_NUMBER() function:

SELECT                                 DENSE_RANK() OVER(ORDER BY O.OrderID) as NewOrderNumber,                                ROW_NUMBER() OVER(PARTITION BY OD.OrderID ORDER BY OD.ProductID) as                               ProductNumber,                                OD.OrderID, OrderDate, OD.ProductId, ProductName, Quantity                              FROM Orders AS O                                JOIN \[Order Details\] AS OD                                  ON O.OrderID = OD.OrderID                                JOIN \[Products\] AS P                                  ON P.ProductID = OD.ProductID

This query produced the expected output.

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to r2r@sqlmag.com. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.