Specify which row is returned on SQLite Group

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. It will increment when 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 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)?

You need to make 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

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

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 947 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC

Leave a Comment

Your email address will not be published.