分组后，选择列最大值的条目 [英] Select entry with maximum value of column after grouping

问题描述

我目前得到这个查询：

I currently got this query:

SELECT 
    `users_sessions`.`userid` `users_sessions`.`lastActive` , `users`.`firstname`
FROM 
    `users_sessions` , `users`
WHERE 
    `users_sessions`.`lastActive` >= DATE_SUB( NOW( ) , INTERVAL 60 MINUTE )
AND 
    `users`.`uidNumber` = `users_sessions`.`userid`

选择 lastActive 为1小时前最大值的所有会话。现在，由于用户可以同时拥有多个会话，因此我只想选择最大 lastActive 值的那个。

It selects all sessions where lastActive is 1 hour ago maximum. Now, as users can have multiple sessions at the same time, I only want to select the one with the biggest lastActive value.

我知道我不知何故必须使用 GROUP BY users_sessions.userid ，然后选择最大的 lastActive 。

I know that I somehow got to use GROUP BY users_sessions.userid and then select the entry with the biggest lastActive.

如何正确执行此操作？

How can I do this properly?

推荐答案

SELECT 
    `users_sessions`.`userid`, `users_sessions`.`lastActive` , `users`.`firstname`
FROM 
    `users_sessions` , `users`
WHERE 
    `users_sessions`.`lastActive` >= DATE_SUB( NOW( ) , INTERVAL 60 MINUTE )
AND 
    `users`.`uidNumber` = `users_sessions`.`userid`
GROUP BY 
    `users_sessions`.`userid`, `users_sessions`.`lastActive` , `users`.`firstname`
HAVING
    `lastActive` = MAX(`lastActive`)

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