CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
adam
adam

Reputation: 800

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

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?

Upvotes: 4

Views: 4591

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

Update after rereading the Q:

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.

If there can be ties (multiple rows with the maximum version per name), you could use the window function rank() in a subquery. Requires PostgreSQL 8.4+.

SELECT *
FROM  (
   SELECT *, rank() OVER (PARTITION BY name ORDER BY version DESC) AS rnk
   FROM   updates 
   WHERE  name IN ('foo', 'bar')
   )
WHERE rnk = 1;

Upvotes: 2

wildplasser
wildplasser

Reputation: 44230

NOT EXISTS is a way to avoid unwanted sub optimal tuples:

SELECT * 
FROM updates uu
WHERE uu.zname IN ('foo', 'bar')
AND NOT EXISTS (
    SELECT *
    FROM updates nx
    WHERE nx.zname = uu.zanme
    AND nx.version > uu.version
    );

Note: I replaced name by zname, since it is more or less a keyword in postgresql.

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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

Upvotes: 5

Related Questions