oracle 根据列的 MAX 值选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5808700/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select rows based on MAX values of a column
提问by aggietech
I am running into a slight problem in Oracle SQL.
我在 Oracle SQL 中遇到了一个小问题。
I have a table that has the following columns (ID, AttributeID, AttributeValue, Version).
我有一个包含以下列（ID、AttributeID、AttributeValue、Version）的表。
I would like to query the table for the latest version given an attribute id.
我想在给定属性 id 的情况下查询最新版本的表。
For example, if I have the following data
例如，如果我有以下数据
ID AttrId AttrValue Version
1 1 A 1
1 1 B 2
1 1 C 3
2 1 F 1
2 2 G 1
If I decided to filter by attribute id 1, I would like to get
如果我决定按属性 id 1 过滤，我想得到
ID AttrId AttrValue Version
1 1 C 3
2 1 F 1
The query will be very simple if I supply the id, because I can filter first by the ID and AttributeID, and then find the max for the version column.
如果我提供 id，查询将非常简单，因为我可以先通过 ID 和 AttributeID 进行过滤，然后找到版本列的最大值。
However, I am still looking and thinking of a solution when the ID is not given, and would like to just get a list based on the AttributeID only.
但是，当没有给出 ID 时，我仍在寻找和思考解决方案，并且只想获取基于 AttributeID 的列表。
Any tips would help greatly!
任何提示都会有很大帮助！
thanks!
谢谢！
采纳答案by Prabhjot
Would this help?
这会有帮助吗？
select * from attributes a
where attribute_id := given_attribute_id
and version_id = (
select max( version_id ) from attributes b
where attribute_id := given_attibute_id
and b.id = a.id
)
回答by Martin Smith
WITH T
AS (SELECT ID,
AttributeID,
AttributeValue,
Version,
row_number() over (PARTITION BY id ORDER BY version DESC) AS rn
FROM your_table
WHERE AttributeID=1)
SELECT ID,
AttributeID,
AttributeValue,
Version
FROM T
WHERE rn = 1;
回答by Thomas
Select ...
From EAV
Where AttributeId = @AttributeId
And Version = (
Select Max( E1.Version )
From EAV As E1
Where E1.Id = EAV.Id
And E1.AttributeId = EAV.AttributeId
)
