0

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`

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.

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?

ekad
  • 14,436
  • 26
  • 44
  • 46
Zulakis
  • 7,859
  • 10
  • 42
  • 67

2 Answers2

1
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`)
Johanna Larsson
  • 10,531
  • 6
  • 39
  • 50
  • In your `GROUP BY` part, you are grouping by all selected columns. In my original query I am selecting lots more columns. Should I also group by all of them? – Zulakis Oct 13 '12 at 13:06
  • You have to group by all selected columns, always. – Johanna Larsson Oct 13 '12 at 13:21
  • In case anyone is looking at this, the answer is incorrect. The `HAVING` clause is comparing an arbitrary value of `lastActive` to the maximum, which may filter out some rows unnecessarily. – Gordon Linoff Aug 13 '13 at 11:32
1

Just select MAX(users_sessions.lastActive) instead of users_sessions.lastActive and use GROUP BY users.uidNumber. It should work.

taufique
  • 2,701
  • 1
  • 26
  • 40
  • 1
    A subtle bug could be introduced by doing this, since the row shown is not necessarily the row that has that lastActive. You show a random row from the group, and then add the lastActive from the row with the highest lastActive. Might not be the same row. – Johanna Larsson Oct 13 '12 at 13:19
  • I selected maximum of lastActive instead of lastActive in the select clause. So as far as I know it shouldn't be the problem you are asking. – taufique Oct 13 '12 at 13:27
  • You get the highest lastActive from the group of rows with the same id, but not the row that has the highest lastActive. That's chosen by sequential order. My answer would get the proper row. In this use case you probably wouldn't notice any bugs, but with more columns you might. – Johanna Larsson Oct 13 '12 at 13:29