-3

I have a table in mysql like this:

+----+------+-----------+----+----+-----+
| id | vers | doc       | pr | dd | f   |
+----+------+-----------+----+----+-----+
| 1  | 1    | doc1.pdf  | 1  | 1  | neg |
+----+------+-----------+----+----+-----+
| 2  | 1    | other.pdf | 1  | 3  | pos |
+----+------+-----------+----+----+-----+
| 3  | 1    | bo.pdf    | 1  | 6  | ok  |
+----+------+-----------+----+----+-----+
| 4  | 2    | doc2.pdf  | 1  | 1  | pos |
+----+------+-----------+----+----+-----+

As you can see, the rows with id 1 and 4 have the same pr and dd value, but with a different vers.

I want retrieve all rows and if I have more rows with the same pr and dd I want get only the row with the maximum value inside the vers column.

So in this example, I want the row with id 2, 3 and 4.

Can I do that using an SQL query? If yes, how?

Jens
  • 67,715
  • 15
  • 98
  • 113
Droide
  • 1,807
  • 2
  • 17
  • 30
  • (SELECT MAX(vers), pr, dd FROM foo GROUP BY pr, dd) gives you the max version you can then join using pr and dd to a select * FROM foo and keep the lines where vers = the max –  Jul 10 '17 at 11:42
  • @RC. He wants the entire row – Blue Jul 10 '17 at 11:43

1 Answers1

4

Have a sub-query that returns each pr/dd combo's max vers. Join with that result:

select t1.*
from tablename t1
join (select pr, dd, max(vers) as vers
      from tablename
      group by pr, dd) t2
  on t1.pr = t2.pr
  and t1.dd = t2.dd
  and t1.vers = t2.vers
jarlh
  • 42,561
  • 8
  • 45
  • 63