Select the maximum value from a GROUP BY operation in CockroachDB?

Question

After grouping rows, how do I also include the maximum value for the group in the return value?

For example, if I have this data:

INSERT INTO orders (customer_id, total) 
VALUES 
(1, 19.99), 
(2, 19.99), 
(1, 5.99), 
(2, 25.99);

I know that I can return each customer using this query:

SELECT customer_id FROM orders GROUP BY customer_id;

But how do I add the maximum value for orders.total to the returned values?

Answer 1

CockroachDB supports the standard SQL syntax for this kind of relationship. The following query produces one result for every customer_id, containing the customer_id and the maximum total for that customer:

SELECT x.customer_id, x.total
FROM orders AS x
JOIN (SELECT o.customer_id,
    MAX(total) AS max_total
    FROM orders o
    GROUP BY o.customer_id) y 
ON y.customer_id = x.customer_id
AND y.max_total = x.total
GROUP BY x.customer_id, x.total;

The result against the sample data is the following:

+-------------+-------+
| customer_id | total |
+-------------+-------+
|           1 | 19.99 |
|           2 | 25.99 |
+-------------+-------+
(2 rows)