1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Daniel Blanco
  • 97
  • 1
  • 8

3 Answers3

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
Nick
  • 138,499
  • 22
  • 57
  • 95
0

One method is a correlated subquery:

select v.*
from versions v
where v.version = (select max(v2.version)
                   from versions v2
                   where v2.str_identifier = v.str_identifier
                  );

You can also use window functions:

select v.*
from (select v.*,
             row_number() over (partition by str_identifier order by version desc) as seqnum
      from versions v
     ) v
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Sounds like rank() should do the trick:

SELECT *
FROM   (SELECT *, RANK() OVER (PARTITION BY str_identifier ORDER BY version DESC) AS rk
        FORM   versions)
WHERE  rk = 1
Mureinik
  • 297,002
  • 52
  • 306
  • 350