eze3B7fm

1CREATE TABLE IF NOT EXISTS `user_actions` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `user` varchar(40) NOT NULL,
4  `action` varchar(40) NOT NULL,
5  `ip` int(11) NOT NULL,
6  `date` datetime NOT NULL,
7  PRIMARY KEY (`id`)
8)
9
10
11
12--TASK:
13-------
14--select active users within the last 10 minutes, and the corresponding action, ip, date.
15
16--PROBLEM:
17----------
18--When running this query:
19
20SELECT user, action, max(date) as last_time FROM user_actions
21GROUP BY user;
22
23--I get the wrong action, i.e. it's one of the user's actions but not his most recent one.
24
25--SOLUTION???
26-------------
27
28SELECT ua1.user, ua1.action, ua1.date as last_seen, ua1.ip
29FROM user_actions AS ua1,
30   (SELECT user, MAX(date) AS maxdate
31	  FROM user_actions
32	 GROUP BY user
33	 HAVING TIMEDIFF(NOW(), max(date)) < '00:10:01') AS ua2
34WHERE ua2.user = ua1.user
35AND ua1.date = ua2.maxdate
36AND ua1.action != 'logout';