根据列的最大值选择行

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进行筛选，然后找到version列的最大值。

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!

谢谢!

3 个解决方案

#1

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 

)

#2

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
                    )

#3

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;