I have a database with three tables Customers, Orders and OrderedProducts. These tables have a one-to-many relationship with each other (one customer can have multiple orders, one order can contain multiple ordered products). Each table has foreign key relationships on identity columns, such that I can get data for one customer like this:

SELECT 
    cus.CustomerId, ord.OrderId, orp.ProductId
FROM 
    Customers cus
INNER JOIN 
    Orders ord ON cus.CustomerId = ord.CustomerId
INNER JOIN 
    OrderedProducts orp ON ord.OrderId = orp.OrderId
WHERE 
    cus.CustomerId = 123
ORDER BY 
    ord.OrderId, orp.ProductId

Output:

My question: what is the easiest way to renumber the OrderId (in my query, not the original table) to reflect that this is the customer's 1st, 2nd, 3rd, etc., order? In other words, instead of seeing "147", "493", "612" in the query above, I'd like to just see "1", "2", "3".

I can't just use ROW_NUMBER(), since there are multiple items for each OrderId.

Any suggestions? For now, assume that the exact SQL dialect doesn't matter, I'm interested in learning any take on this. Thanks!

CodePudding user response：

WITH CTE(CustomerId, OrderId, ProductId) AS
(  
    SELECT 123, 147, 6  UNION ALL
    SELECT 123, 147, 13 UNION ALL
    SELECT 123, 147, 18 UNION ALL
    SELECT 123, 493, 14 UNION ALL
    SELECT 123, 493, 18 UNION ALL
    SELECT 123, 612, 2 UNION ALL
    SELECT 123, 612, 11 UNION ALL
    SELECT 123, 612, 16 UNION ALL
    SELECT 123, 612, 23
)
SELECT 
    C.CustomerId, C.OrderId, C.ProductId,
    DENSE_RANK() OVER (PARTITION BY C.CustomerId ORDER BY C.OrderId ASC) XCOL
FROM 
    CTE AS C

CodePudding user response：

You do need a ROW_NUMBER() but with a window function!:

SELECT cus.CustomerId, 
ROW_number() OVER (
    partition by cus.CustomerId, ord.OrderId 
    order by ord.OrderId) 
as order_no, -- magic OVER keyword!
ord.OrderId,
orp.ProductId
    FROM Customers cus
INNER JOIN Orders ord
    ON cus.CustomerId = ord.CustomerId
INNER JOIN OrderedProducts orp
    ON ord.OrderId = orp.OrderId
WHERE cus.CustomerId = 123
ORDER BY ord.OrderId, orp.ProductId

Windows function would run some function (row_number() in this case) over a subset of returned records. Subset is defined by partition by and if order is important, add order by.

Actually, I am wrong. Use Sergey's solution! Above query would add numbers for products, not for orders.

SELECT cus.CustomerId, 
DENSE_RANK() OVER (
    partition by cus.CustomerId
    order by ord.OrderId) 
as order_no, -- order number    
ROW_NUMBER() OVER (
    partition by cus.CustomerId, ord.OrderId 
    order by ord.OrderId) 
as product_no, -- product number
ord.OrderId,
orp.ProductId
    FROM Customers cus
INNER JOIN Orders ord
    ON cus.CustomerId = ord.CustomerId
INNER JOIN OrderedProducts orp
    ON ord.OrderId = orp.OrderId
WHERE cus.CustomerId = 123
ORDER BY ord.OrderId, orp.ProductId

More information (every vendor has small variations what part of spec is implemented): SQL Server window functions MySQL window functions Oracle Analytical functions