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.