All geek questions in one place

Specify Which String to Return to SQLite Group By

I ran into some kind of difficult problem. I save all versions of all documents in one table. Each document has a unique identifier, and the version is stored as an integer, which increases each time a new version appears.

I need a query that will select only the latest version of each document from the database. When using GROUP BY, it seems that it will break if versions are not inserted into the database in version order (i.e., it accepts the maximum ROWID, which will not always be the latest version).

Note that the latest version of each document is likely to be different from the number (that is, document A is in version 3, and document B is in version 6).

I'm in my mind, does anyone know how to do this (select all documents, but return only one entry for each document_id, and that the returned entry should have the highest version number)?

+4
source share
1 answer

You need to make a subtitle to find the maximum version, so something like

Suppose 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
+6
source

Source: https://habr.com/ru/post/1310087/

More articles:


All Articles