Mysql get latest record/row based on another columns latest/max

Question

I have a table like this

id	name	version	ref_id	deleted
1	a	1	1	1
2	b	3	1	0
3	c	2	1	1
4	a	3	2	1
5	bb	1	2	0
6	cc	2	2	0

what I would like to achieve is to select the rows with the latest versions

id	name	version	ref_id	deleted
2	b	3	1	0
4	a	3	2	1

This is my original approach but is too slow for our system now:

select t.*
from (
    select ref_id, max(version) as version
    from table1
    group by ref_id
) latest
inner join table1 t on t.ref_id = latest.ref_id and t.version = latest.version

Is there a way to do something like:

select if(version = max(version), id, other columns) from table group by ref_id ?

Answer 1

In MySQL 8 or later you can use row_number window function:

with cte as (
    select *, row_number() over (partition by ref_id order by version desc) as rn
    from t
)
select *
from t
where rn = 1

For earlier versions of MySQL your existing approach is best but an alternate solution worth trying:

select *
from t
where (ref_id, version) in (
    select ref_id, max(version)
    from t
    group by ref_id
)