Query Problem Solved with a Little Help from a New Friend

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.

—Luciano Caixeta
[email protected]

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 [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.

Comments

Plain text