选择满足某些条件并在特定列中具有最大值的行 [英] select rows satisfying some criteria and with maximum value in a certain column

问题描述

我有一个元数据表，用于更新软件包。该表具有列 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

这篇关于选择满足某些条件并在特定列中具有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！