GROUP BY - 排序分组前后

I am HAVING trouble with my GROUP BY query (the trickiest of them all at least for me).

我的GROUP BY查询遇到了麻烦(其中最棘手的是至少对我而言)。

What I want to do is retrieve the most recent modified records grouped by another column. The problem as I see it is that grouping returns the only first record it finds (in the group) disregarding the ORDER BY (except when returning the final result).

我想要做的是检索由另一列分组的最新修改记录。我看到的问题是,分组返回它找到的唯一第一条记录(在组中)忽略ORDER BY(返回最终结果时除外)。

Here is a simplified version of my code.

这是我的代码的简化版本。

SELECT events.id, events.name, events.type, events.modified_time
FROM events
GROUP BY events.type
ORDER BY event.modified_time DESC

Which will return:

哪个将返回:

1 | Event One   | Birthday   | Jan 1, 2012  
2 | Event Two   | Graduation | Jan 1, 2012

When in fact there is a 3rd record with a modified time that is later:

事实上,第三条记录的修改时间是晚些时候:

3 | Event Three | Birthday   | Jan 2, 2012

I've tried using a HAVING clause of MAX(modified_time) but it doesn't return the correct results either. Maybe I just need to discuss this more with someone who is knowledgeable but if the question makes enough sense and you can tell what I need then maybe there is an easy answer.

我尝试过使用MAX的HAVING子句(modified_time),但它也没有返回正确的结果。也许我只需要与知识渊博的人讨论这个问题,但如果这个问题足够有意义,你可以告诉我需要什么,那么也许有一个简单的答案。

BONUS QUESTION:
Is it possible to do this without a sub query?

奖金问题:没有子查询可以做到这一点吗?

2 个解决方案

#1

This, among many other methods, may work for you:

在许多其他方法中,这可能对您有用:

SELECT a.id, a.name, a.[type], a.modified_time
FROM [events] AS a
    JOIN (
        SELECT MAX([events].id) AS id, [events].[type]
        FROM [events]
        GROUP BY [events].[type]    
    ) AS b ON a.id = b.id AND a.[type] = b.[type]
ORDER BY a.modified_time DESC

#2

GROUP BY/HAVING is not what you want. What you want is either:

GROUP BY / HAVING不是你想要的。你想要的是:

WHERE
    events.modified_time = ( select max(modified_time) from events e2 where e2.type = events.type )

... or else a solution like Tim linked to using an analytic function like ROW_NUMBER

...或者像蒂姆这样的解决方案与使用像ROW_NUMBER这样的分析函数相关联

#1