SQL:筛选具有最大值的行 [英] SQL: Filter rows with max value

问题描述

这是我的表结构:

File    |   Version     |   Function
1       |   1           |   1
1       |   2           |   1
1       |   3           |   1
1       |   2           |   2

2       |   1           |   4
3       |   2           |   5

我需要它仅返回这些行

1       |   3           |   1
2       |   1           |   4
3       |   2           |   5

意思是我只想要每个文件具有最新版本的功能.

Meaning I only want the functions that have the most recent version for each file.

我不希望看到以下结果，即不是最新版本的唯一函数ID

I do not want the result below, i.e unique function ids that are not the most recent version

1       |   3           |   1
1       |   2           |   2
...

我看过

I've looked at How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?, but that returns the most recent unique function ids.

查询必须与sqlite3兼容.

The query needs to be sqlite3 compatible.

推荐答案

一种有效的方法通常是使用not exists:

An efficient way to do this is often to use not exists:

select t.*
from table t
where not exists (select 1
                  from table t2
                  where t2.file = t.file and t2.Version > t.version
                 );

此查询可以利用table(file, version)上的索引.

This query can take advantage of an index on table(file, version).

这将查询重新表述为:从表中获取对应文件没有较大版本的所有行."

This rephrases the query to be: "Get me all rows from the table where the corresponding file has no larger version."

这篇关于SQL:筛选具有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！