Retrieve latest document revisions in MySQL

Data and Databases
1

I’m working with a document table in MySQL that looks like this:

| doc_id | version | data |
|--------|---------|------|
| 1      | 1       | ...  |
| 2      | 1       | ...  |
| 1      | 2       | ...  |
| 1      | 3       | ...  |

I need to get the most recent version of each document. For the sample data above, I want to fetch these rows:

| doc_id | version | data |
|--------|---------|------|
| 1      | 3       | ...  |
| 2      | 1       | ...  |

Right now, I’m using a while loop to check and update old versions in my result set. But I’m wondering if there’s a better way to do this directly in SQL. Can anyone help me write a query to get the latest version of each document in one go?

2

hey there! u can use a subquery with MAX to grab the latest versions. try this:

SELECT d1.*
FROM documents d1
JOIN (SELECT doc_id, MAX(version) as max_version FROM documents GROUP BY doc_id) d2
ON d1.doc_id = d2.doc_id AND d1.version = d2.max_version

this should do the trick for ya! lemme kno if u need more help

3

ooh, interesting problem! have u considered using window functions? something like:

SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY doc_id ORDER BY version DESC) as rn
FROM documents
) sub
WHERE rn = 1

what do u think? it might be more efficient for larger datasets. curious to hear ur thoughts!