Specify which row to return on SQLite Group By
- by lozzar
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)?