根据另一个值的总和来查询唯一值，同时将第三个值完全分组 [英] Querying for a unique value based on the aggregate of another value while grouping on a third value entirely

问题描述

所以我知道这个问题不是一个新问题，但我正在努力解决这个问题，并了解应对此类情况的最佳方法。

So I know this problem isn't a new one, but I'm trying to wrap my head around it and understand the best way to deal with scenarios like this.

说我有一个假设表'X'，看起来像这样：

Say I have a hypothetical table 'X' that looks like this:

GroupID ID (identity)   SomeDateTime
--------------------------------------------
1       1000        1/1/01
1       1001        2/2/02
1       1002        3/3/03
2       1003        4/4/04
2       1004        5/5/05

我要查询它，因此结果集如下所示：

I want to query it so the result set looks like this:

----------------------------------------
1       1002        3/3/03
2       1004        5/5/05

基本上我想要的是由我的 GroupID 列分组的MAX SomeDateTime 值。更重要的是，我不想按 ID 列分组，
我只想知道与MAX <$ c相对应的'ID' $ c> SomeDateTime 。

Basically what I want is the MAX SomeDateTime value grouped by my GroupID column. The kicker is that I DON'T want to group by the ID column, I just want to know the 'ID' that corresponds to the MAX SomeDateTime.

我知道一个伪解决方案是：

I know one pseudo-solution would be:

;WITH X1 as (
    SELECT MAX(SomeDateTime) as SomeDateTime, GroupID 
    FROM X
    GROUP BY GroupID
)
SELECT X1.SomeDateTime, X1.GroupID, X2.ID
FROM X1
    INNER JOIN X as X2
        ON X.DateTime = X2.DateTime

但这不能解决DateTime可能不是唯一的事实。加入这样的DateTime似乎很草率。

But this doesn't solve the fact that a DateTime might not be unique. And it seems sloppy to join on a DateTime like that.

另一个伪解决方案可能是：

Another pseudo-solution could be:

SELECT X.GroupID, MAX(X.ID) as ID, MAX(X.SomeDateTime) as SomeDateTime
FROM X
GROUP BY X.GroupID

但不能保证 ID 会与 SomeDateTime 来自。

第三个不太有用的选项可能是：

A third less useful option might be:

SELECT TOP 1 X.GroupID, X.ID, X.SomeDateTime
FROM X
WHERE X.GroupID = 1
ORDER BY X.SomeDateTime DESC

但是显然，这仅适用于单个已知的 GroupID 。我希望能够在 GroupID 和/或 ID 上加入此结果集。

But obviously that only works with a single, known, GroupID. I want to be able to join this result set on GroupID and/or ID.

有人知道任何聪明的解决方案吗？

Does anyone know of any clever solutions? Any good uses of windowing functions?

谢谢！

推荐答案

I

;WITH X1 
AS 
(
    SELECT SomeDateTime
           ,GroupID 
           ,ID
           ,ROW_NUMBER() OVER (PARTITION BY GroupID
                               ORDER BY SomeDateTime DESC
                               ) AS rn
    FROM X
)
SELECT SomeDateTime
       ,GroupID
       ,ID
FROM X1
WHERE rn = 1

这篇关于根据另一个值的总和来查询唯一值，同时将第三个值完全分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！