Specify which row to return on SQLite Group By

Posted by lozzar on Stack Overflow See other posts from Stack Overflow or by lozzar
Published on 2010-05-18T08:16:05Z Indexed on 2010/05/18 8:20 UTC
Read the original article Hit count: 437

Filed under:
|
|
|
|

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)?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sqlite