Select rows that has the same value in one column and different in other

Question

I have a table with data of multiple versions. Each row has an identifier and a version. I need to select the rows with the higher version, but not each rows have the same version. For example:

    str_identifier | version              | other_data
------------------ |---------------------
abcd               | 1
abcd               | 3
abcd               | 3
qqqq               | 2
qqqq               | 4
rrrr               | 1
rrrr               | 1

I need to select the rows abcd with version 3, the row qqqq with version 4 and the rows rrrr with version 1. Each str_identifier has multiple records for each version, that's why they are repeated.

Thank you in advance.

Answer 1

You can use a JOIN to a derived table of maximum version numbers to get all the rows which have that maximum value:

SELECT v.*
FROM versions v
JOIN (SELECT str_identifier, MAX(version) AS version
      FROM versions
      GROUP BY str_identifier) m ON m.str_identifier = v.str_identifier AND m.version = v.version