Geek Answers Handbook

Select rows that meet certain criteria and with a maximum value in a specific column.

I have a metadata table for software package updates. The table has columns id, name, version . I want to select all the lines in which the name is one of the defined lists of names and the version is the maximum of all lines with that name.

For example, given these entries:

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

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

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

I am currently 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) );

It works, but it feels wrong. If I want to filter more names, I have to replicate the entire subquery several times. Is there a better way to do this?

+4
source share
3 answers
 select distinct on (name) id, name, version from metadata where name in ('foo', 'bar') order by name, version desc
+4
source

NOT EXISTS is a way to avoid unwanted suboptimal 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 with zname , as it is more or less a keyword in postgresql.

+3
source

Update after rewriting Q:

I want to select all the lines where the name is one of some given lists of names and the version of the maximum of all lines with this name .

If there can be links (several lines with the maximum version on name ), you can use the rank() window function in a subquery. PostgreSQL 8.4+ required.

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

More articles:


All Articles