I have the following MySQL table:
[orders]
===
order_id BIGINT UNSIGNED AUTO INCREMENT (primary key)
order_ref_id VARCHAR(36) NOT NULL,
order_version BIGINT NOT NULL,
order_name VARCHAR(100) NOT NULL
...
lots of other fields
The orders are "versioned" meaning multiple orders can have the same identical order_ref_id but will have different versions (version 0, version 1, version 2, etc.).
I want to write a query that returns all order fields for an order based on its order_ref_id and its MAX version, so something like this:
SELECT
*
FROM
orders
WHERE
order_ref_id = '12345'
AND
MAX(order_version)
In other words: given the org_ref_id, give me the single orders record that has the highest version number. Hence if the following rows exist in the table:
order_id | order_ref_id |. order_version | order_name
======================================================================
1 | 12345 | 0 | "Hello"
2 | 12345 | 1 | "Goodbye"
3 | 12345 | 2 | "Wazzup"
I want a query that will return:
3 | 12345 | 2 | "Wazzup"
However the above query is invalid, and yields:
ERROR 1111 (HY000): Invalid use of group function
I know typical MAX() examples would have me writing something like:
SELECT
MAX(order_version)
FROM
orders
WHERE
order_ref_id = '12345';
But that just gives me order_version, not all (*) the fields. Can anyone help nudge me across the finish line here?
You could try using a subquery for max version group by order_ref_id
select * from orders o
inner join (
SELECT order_ref_id
, MAX(order_version) max_ver
FROM orders
group by order_ref_id
) t on t.order_ref_id = o.order_ref_id
and t.max_ver = o.order_version