我有一个元数据表,用于更新软件包。该表具有列 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
更多推荐
选择满足某些条件并在特定列中具有最大值的行
发布评论