获取列的最大值所在的行 [英] Fetch the row which has the Max value for a column

问题描述

表:

UserId, Value, Date.

我想获取每个 UserId 的用户 ID，最大值(日期)的值.即，每个具有最新日期的 UserId 的值.有没有办法简单地在 SQL 中做到这一点?(最好是甲骨文)

I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle)

更新:对任何含糊之处表示歉意:我需要获取所有用户 ID.但是对于每个 UserId，只有该用户具有最新日期的那一行.

Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.

推荐答案

这将检索 my_date 列值等于该用户 ID 的 my_date 最大值的所有行.这可能会为用户 ID 检索多行，其中最大日期在多行上.

This will retrieve all rows for which the my_date column value is equal to the maximum value of my_date for that userid. This may retrieve multiple rows for the userid where the maximum date is on multiple rows.

select userid,
       my_date,
       ...
from
(
select userid,
       my_date,
       ...
       max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

分析函数摇滚"

关于第一条评论...

使用分析查询和自联接违背了分析查询的目的"

"using analytic queries and a self-join defeats the purpose of analytic queries"

此代码中没有自联接.相反，在包含分析函数的内联视图的结果上放置了一个谓词——这是一个非常不同的问题，并且是完全标准的做法.

There is no self-join in this code. There is instead a predicate placed on the result of the inline view that contains the analytic function -- a very different matter, and completely standard practice.

"Oracle中默认窗口是从分区的第一行到当前行"

"The default window in Oracle is from the first row in the partition to the current one"

windowing 子句仅适用于存在 order by 子句的情况.没有order by子句，默认不应用windowing子句，也不能显式指定.

The windowing clause is only applicable in the presence of the order by clause. With no order by clause, no windowing clause is applied by default and none can be explicitly specified.

代码有效.

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