How do I get all the latest "versions" from my table with one MySQL query?
I have a table with the following basic structure.
unique_id | name | original | version
-----------------
1 | a1 | 1 | 1
2 | b1 | 2 | 1
3 | a2 | 1 | 2
4 | a3 | 1 | 3
5 | c1 | 5 | 1
6 | b2 | 2 | 2
It should now be obvious from this that there is a versioning form where we keep track of the original document as well as keep track of the version of the current document.
If I want to get the latest version of a specific document, I do something like the following.
SELECT * FROM table WHERE original = (SELECT original FROM table WHERE id = 3) ORDER BY version DESC
My question is, how do I get a list of all recent versions in a table with only one query?
+2
source to share
2 answers
The idea is
- create a list of all original ids with the current maximum version
- join the table with this list of unique identifiers.
SELECT *
FROM table t
INNER JOIN (
SELECT original, MAX(version) as version
FROM tabel
GROUP BY original
) tmax ON tmax.original = t.original and tmax.version = t.version
+2
source to share