How to group records by maximum value in cell?

Question

MySQL

There is a table that looks something like this:

| title | version | identity |
|-------|---------|----------|
| Av1   | 1       | some     |
| Av2   | 2       | some     |
| Bv1   | 1       | any      |
| Bv2   | 2       | any      |
| Bv3   | 3       | any      |

Where "title" is just a random title for the example, "version" is an incremental field pointing to a newer version in the series, "identity" is a post identifier (i.e. some identifier that indicates that N is the same element , but differs only in the version number and, as a result, in some other things)
I'm trying to make a simple query that would receive the following result:

| Av2 | 2 | some |
| Bv3 | 3 | any  |

Those. the latest version (version) from the series (group by identity).
Naturally, such a query does not work in the case of a pug (which is required), although it is reproduced on sqlite:

SELECT * FROM mytable GROUP BY identity ORDER BY version DESC;

* I assume that the very first option will be included in the grouping result (and they are sorted by version in reverse order).
And further I do not represent already as without subqueries. Any ideas how to make a similar query as a simple selection without subqueries?

Answer 1

SELECT mt.* FROM mytable AS mt
INNER JOIN (
    SELECT MAX(version) AS max_version FROM mytable GROUP BY identity
) AS grp
ON mt.identity = grp.identity AND mt.version = grp.max_version;

Answer 2

select * from ( select * from mytable order by version desc ) as a group by identity