SQL - Tuple Comparison

-- 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';