Get the Latest (or Top) Record per Group

-- Example: Get the latest order for each customer
SELECT
    customer_id,
    order_id,
    order_date,
    total_amount
FROM (
        SELECT
            customer_id,
            order_id,
            order_date,
            total_amount,
            ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) as rn
        FROM
            orders
    ) AS ranked_orders
WHERE rn = 1;

How it works: This snippet demonstrates how to retrieve the latest record for each distinct group (e.g., the latest order for each customer). It uses the ROW_NUMBER() window function with PARTITION BY customer_id and ORDER BY order_date DESC, order_id DESC to assign a rank to each order within a customer's set of orders. Filtering WHERE rn = 1 then selects only the latest order for each customer.