Specify which row to return on SQLite Group By

Question

I'm faced with a bit of a difficult problem. I store all the versions of all documents in a single table. Each document has a unique id, and the version is stored as an integer which is incremented everytime there is a new version.

I need a query that will only select the latest version of each document from the database. While using GROUP BY works, it appears that it will break if the versions are not inserted in the database in the order of version (ie. it takes the maximum ROWID which will not always be the latest version).

Note, that the latest version of each document will most likely be a different number (ie. document A is at version 3, and document B is at version 6).

I'm at my wits end, does anybody know how to do this (select all the documents, but only return a single record for each document_id, and that the record returned should have the highest version number)?

Answer 1

You need to do a subselect to find the max version so something like

Assume 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