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





<b>\[Order Details\] in Northwind      Expected Query Output</b>
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.