llanato llanato - 1 year ago 55
MySQL Question

Get latest version of record in MySQL

I'm trying to get the latest version for each record. For example I'm trying to get just version 2 of id 13 instead of both version 1 and 2, I've tried using a sub query with teh

function but it didn't make any different.

The primary key on the table
PRIMARY KEY(ass_id, ass_version)

** SQL Query **

SELECT `ass_id` AS `id`
, `ass_title` AS `name`
, `ass_version` AS `version`
FROM `td_asset`
GROUP BY `ass_id`, `ass_version`
ORDER BY `id` DESC, `ass_version` DESC;

SQL Query Result

id name version
13 test name 2
13 test name 1
12 test name 1
11 test name 1
10 test name 1
9 test name 1
8 test name 1
7 test name 1
6 test name 1
5 test name 1
4 test name 1
3 test name 1
2 test name 1
1 test name 1

Any suggestions/ideas greatly appreciated.

Answer Source

While this question is asked a lot, it can be sometimes difficult to adapt other people's queries to your situation. So, one way to handle this is with a subquery.

    tda.ass_id AS id,
    tda.ass_title AS name,
    tda.ass_version AS version
FROM td_asset tda
WHERE tda.version = (SELECT MAX(tda2.version) FROM td_asset td2 WHERE tda2.id = tda.id)
GROUP BY tda.ass_id

This essentially says, with each id, look up the max version and use that record.

There are other ways of solving this problem as well, this is just one of them.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download