Specifying MAX group function in MySQL for versioned data

Question

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?

Answer 1

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