MySQL Select max on multiple values?

Question

I have a table like:

file   MajorVersion MinorVersion
a      0            1
b      0            1
a      0            2
a      0            3
b      1            0
a      1            0
b      1            1

I would like to get each file's latest version (highest minor version of the highest major version). In this case:

a 1 0
b 1 1

Its seems possible with two joins and group by file, but I thought there maybe a better way. Maybe by use of having?

Answer 1

No-JOIN version for minor versions below 10:

SELECT file, MAX(CAST(CONCAT(Majorversion, '.', MinorVersion) AS DECIMAL(5,1))) as version
FROM my_table
group by file

Results:

file    version
a       3.0
b       1.1

Minor versions >= 10 would need some extra formatting :-)

Answer 2

Two join method is:

select t1.* from `table` as t1
JOIN
(
  select `file` , max(`minor`) as `minor` from `table` 
  group by `file` , `major` 
) as t2
on t1.`file` = t2.`file` and t1.`minor` = t2.`minor` and t1.`major` = t2.`major`
JOIN
(
select `file` , max(`major`) as `major` , `minor` from `table` 
group by `file`
)  as t3
on t1.`file = t3.`file` and t1.`major` = t3.`major`

Answer 3

Only one join required:

SELECT   file, MajorVersion, MAX(MinorVersion) MinorVersion
FROM     my_table NATURAL JOIN (
  SELECT   file, MAX(MajorVersion) MajorVersion
  FROM     my_table
  GROUP BY file
) t
GROUP BY file

See it on sqlfiddle.