I need a query that selects only the latest version of each document from the database. When working with GROUP BY, if the version is not inserted into the database in the order of the version , It seems to break (i.e. it needs the largest ROWID, not always the latest version).
Please note that the latest version of each document is likely to be a different number (i.e. the version of document A Is 3 and the version of document B is 6).
I’m at the end of my wisdom, someone knows how to do this (select all documents, but only return one record for each document_id, and return The record should have the highest version number)?
Assuming the table is called docs
select t.*
from docs t
inner join (select id, max(version) as ver from docs group by id) as t2
where t2.id = t.id and t2.ver = t.version
I encountered a tricky problem. I store all versions of all documents in a table. Each document has a unique id, and the version is stored as an integer, and it is incremented whenever there is a new version.
I need a query that selects only the latest version of each document from the database. When working with GROUP BY, if the versions are not inserted into the database in version order, it seems to break (i.e. it needs the largest ROWID, not always the latest Version).
Please note that the latest version of each document is likely to have a different number (that is, the version of document A is 3, and the version of document B is 6).
I At the end of my wisdom, does anyone know how to do this (select all documents, but only return one record for each document_id, and the returned record should have the highest version number)?
You need to do a subselect to find the largest version, like this
Assuming the table is called docs
select t.*
from docs t
inner join (select id, max(version) as ver from docs group by id) as t2
where t2.id = t.id and t2.ver = t.version