select rows satisfying some criteria and with maximum value in a certain column

Question

I have a table of metadata for updates to a software package. The table has columns id, name, version. I want to select all rows where the name is one of some given list of names and the version is maximum of all the rows with that name.

For example, given these records:

+----+------+---------+
| id | name | version |
+----+------+---------+
| 1  | foo  | 1       |
| 2  | foo  | 2       |
| 3  | bar  | 4       |
| 4  | bar  | 5       |
+----+------+---------+

And a task "give me the highest versions of records "foo" and "bar", I want the result to be:

+----+------+---------+
| id | name | version |
+----+------+---------+
| 2  | foo  | 2       |
| 4  | bar  | 5       |
+----+------+---------+

What I come up with so far, is using nested queries:

SELECT * 
  FROM updates 
  WHERE (
    id IN (SELECT id 
             FROM updates 
             WHERE name = 'foo' 
             ORDER BY version DESC 
             LIMIT 1)
  ) OR (
    id IN (SELECT id 
             FROM updates 
             WHERE name = 'bar' 
             ORDER BY version DESC 
             LIMIT 1)
  );

This works, but feels wrong. If I want to filter on more names, I have to replicate the whole subquery multiple times. Is there a better way to do this?

Answer 1

select distinct on (name) id, name, version
from metadata
where name in ('foo', 'bar')
order by name, version desc