如何仅选择SQL中具有不同版本的文档列表的最大版本? [英] How do you select only the maximum version of a list of documents that have different versions in SQL?
问题描述
我有一个看起来像这样的数据库表:
I have a database table that looks like this:
| ID | TITLE | VERSION |
| 1 | file1 | 1 |
| 2 | file2 | 1 |
| 3 | file1 | 2 |
| 4 | file2 | 2 |
我需要一个SQL查询,该查询将返回第3行和第4行,因为它们是file1和file 2的最新版本.
I need an SQL query that will return rows 3 and 4 because they are the latest versions of file1 and file 2.
如果我在如下所示的表上运行查询:
If I run the query on a table that looks like this:
| ID | TITLE | VERSION |
| 1 | file1 | 1 |
| 2 | file2 | 1 |
| 3 | file1 | 2 |
| 4 | file2 | 2 |
| 5 | file3 | 1 |
它应该返回第3、4和5行,因为"file3" version1是file3的最新版本.
It should return rows 3,4 and 5 because "file3" version1 is the latest version of file3.
我知道我需要在SQL中使用"MAX"函数,但是我被"GROUP BY"关键字抛弃了.我对如何使用它不是很熟悉.
I know I need to use "MAX" function in SQL, however I'm getting throw off with the "GROUP BY" keyword. I'm not very familiar with how to use it.
将感谢所有/任何建议!
Would appreciate all / any advice!
我们正在使用Oracle 11g.
We are using Oracle 11g.
推荐答案
的确,使用子查询获取由TITLE
分组的MAX
版本,然后将其结果与表连接以获得ID
:
Indeed, use a subquery to obtain the MAX
version, grouped by TITLE
, and then join the result of it with your table to obtain the ID
:
SELECT t.*
FROM tbl t INNER JOIN
(SELECT title, MAX(version) version
FROM tbl
GROUP BY title
) max_t ON (t.version = max_t.version AND t.title = max_t.title);
DEMO .
DEMO.
这篇关于如何仅选择SQL中具有不同版本的文档列表的最大版本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!