5. Common Table Expression (CTE)

Here’s a real-world example that involves using a Common Table Expression (CTE) for ranking records. Let’s consider a scenario where we have two tables:

1. Orders Table (Table1): This table stores customer orders.
2. OrderHistory Table (Table2): This table stores the history of changes to those orders.

We want to join the latest update for each order (from the OrderHistory table) with the corresponding customer order (from the Orders table).

Schema of the Tables

Orders (Table1):

id	customer_id	order_date	value
101	1	2025-01-01	500
102	2	2025-01-02	300
103	3	2025-01-03	700

OrderHistory (Table2):

id	order_id	update_date	status
1	101	2025-01-01	Shipped
2	101	2025-01-02	Delivered
3	102	2025-01-02	Shipped
4	103	2025-01-04	Pending
5	103	2025-01-05	Shipped

Goal

We want to:

• Retrieve all orders.
• Show the latest status of each order, based on the most recent update from the OrderHistory table.

We can achieve this using a CTE with ROW_NUMBER() to rank the updates for each order by the most recent update_date and then join this with the Orders table.

আমরা এখানে OrderHistory থেকে সবচেয়ে আপডেট স্টাটাস জানবো।

SQL Query with CTE

WITH RankedOrderHistory AS (
    SELECT 
        id, 
        order_id, 
        status, 
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_date DESC) AS rn
    FROM OrderHistory
)
SELECT 
    o.id, 
    o.customer_id, 
    o.order_date, 
    o.value, 
    roh.status
FROM 
    Orders o
LEFT JOIN 
    RankedOrderHistory roh
ON 
    o.id = roh.order_id AND roh.rn = 1;

Explanation:

• CTE (RankedOrderHistory): This CTE ranks the order history for each order_id by the update_date in descending order. This way, the most recent status gets the row number 1.
• Main Query: Joins the Orders table with the CTE (RankedOrderHistory) to get the latest status (rn = 1) for each order.

Resulting Output

id	customer_id	order_date	value	status
101	1	2025-01-01	500	Delivered
102	2	2025-01-02	300	Shipped
103	3	2025-01-03	700	Shipped

Explanation of Output:

• For Order 101, the latest update is “Delivered” (most recent update_date of 2025-01-02).
• For Order 102, the latest update is “Shipped” (most recent update_date of 2025-01-02).
• For Order 103, the latest update is “Shipped” (most recent update_date of 2025-01-05).

The use of CTE and ROW_NUMBER() ensures that only the most recent status per order is selected.