SQL - Tuple Comparison

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id numeric NOT null,
  order_date date NOT null,
  order_status VARCHAR(25) NOT null
);

INSERT INTO orders(customer_id, order_date, order_status) VALUES 
(101, '2024-01-01', 'shipped'),
(101, '2024-02-01', 'shipped'),
(102, '2024-02-01', 'shipped'),
(102, '2024-02-10', 'waiting'),
(103, '2024-01-15', 'shipped'),
(103, '2024-02-15', 'shipped'),
(104, '2024-02-15', 'waiting');

-- Using Tuple Comparison
SELECT o.order_id, o.customer_id, o.order_date, o.order_status
FROM orders o
WHERE (o.customer_id, o.order_date) IN (
    SELECT customer_id, MAX(order_date)
    FROM orders
    WHERE order_status = 'shipped'
    GROUP BY customer_id
);

-- Using JOIN
SELECT order_id, customer_id, order_date, order_status
FROM orders 
JOIN (
    SELECT customer_id, MAX(order_date) AS max_order_date
    FROM orders
    WHERE order_status = 'shipped'
    GROUP BY customer_id
) recent_orders
ON customer_id = recent_orders.customer_id
AND order_date = recent_orders.max_order_date
WHERE order_status = 'shipped';

order_id	customer_id	order_date	order_status
2	101	2024-02-01	shipped
3	102	2024-02-01	shipped
6	103	2024-02-15	shipped

CREATE TABLE
INSERT 0 7