选择满足某些条件并在特定列中具有最大值的行

本文介绍了选择满足某些条件并在特定列中具有最大值的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个元数据表，用于更新软件包。该表具有列 id，名称，版本。我要选择名称是给定名称列表之一的所有行，并且该版本是具有该名称的所有行的最大值。

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.

例如，给定这些记录：

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

还有一个任务给我记录的最高版本 foo和 bar，我希望结果是：

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?

推荐答案

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