选择不同的行，同时按最大值分组 [英] Select distinct rows whilst grouping by max value

问题描述

我目前有下表:

ID   |  Name    |  EventTime            |  State
1001 |  User 1  |  2013/07/22 00:00:05  |  15
1002 |  User 2  |  2013/07/23 00:10:00  |  100
1003 |  User 3  |  2013/07/23 06:15:31  |  35
1001 |  User 1  |  2013/07/23 07:13:00  |  21
1001 |  User 1  |  2013/07/23 08:15:00  |  25
1003 |  User 3  |  2013/07/23 10:00:00  |  22
1002 |  User 2  |  2013/07/23 09:18:21  |  50

我需要的是与上一个eventtime不同的每个userid的state，如下所示:

What I need is the state for each distinct userid from the last eventtime similar to below:

ID   |  Name    |  EventTime            |  State
1001 |  User 1  |  2013/07/23 08:15:00  |  25
1003 |  User 3  |  2013/07/23 10:00:00  |  22
1002 |  User 2  |  2013/07/23 09:18:21  |  50

我需要类似以下内容的东西，但我无法完全满足需要.

I need something similar to the following but I can't quite get what I need.

SELECT ID, Name, max(EventTime), State
FROM MyTable
GROUP BY ID

推荐答案

在支持分析功能的数据库中，可以使用row_number():

In databases that support analytic functions, you could use row_number():

select  *
from    (
        select  row_number() over (partition by ID 
                                   order by EventTime desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn = 1

这篇关于选择不同的行，同时按最大值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！