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

问题描述

表格:

UserId, Value, Date.

我想获取UserId，每个UserId的max(Date)值.即，具有最新日期的每个UserId的值.有没有一种方法可以简单地在SQL中做到这一点? (最好是Oracle)

更新:对于任何歧义，我们深表歉意:我需要获取所有UserId.但是对于每个UserId，仅该用户具有最新日期的那一行.

解决方案

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

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

分析功能强大"

关于第一个评论...

使用分析查询和自联接会破坏分析查询的目的"

此代码中没有自联接.而是在包含分析功能的内联视图结果上放置一个谓词-这是完全不同的事情，并且是完全标准的实践.

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

windowing子句仅在order by子句存在的情况下适用.没有order by子句，默认情况下不应用任何窗口条款，并且不能明确指定任何子句.

代码有效.

Table:

UserId, Value, Date.

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)

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.

解决方案

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

"Analytic functions rock"

Edit: With regard to the first comment ...

"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.

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

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.

The code works.

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