Best way to construct this query?

Question

I have two tables set up similar to this (simplified for the quest):

actions-

id - user_id - action - time

users -

id - name

I want to output the latest action for each user. I have no idea how to go about it.

I'm not great with SQL, but from what I've looked up, it should look something like the following. not sure though.

SELECT `users`.`name`, *
FROM users, actions
JOIN < not sure what to put here >
ORDER BY `actions`.`time` DESC
< only one per user_id >

Any help would be appreciated.

Answer 1

SELECT * FROM users JOIN actions ON actions.id=(SELECT id FROM actions WHERE user_id=users.id ORDER BY time DESC LIMIT 1);

Answer 2

select u.name, a.action, a.time
from user u, action a
where u.id = a.user_id
and a.time in (select max(time) from action where user_id = u.user_id group by user_id )

note untested - but this should be the pattern

Answer 3

you need to do a groupwise max - please refer to examples here http://jan.kneschke.de/projects/mysql/groupwise-max/

here's an example i did for somone else which is similar to your requirements:

http://pastie.org/925108

select
 u.user_id,
 u.username,
 latest.comment_id
from
 users u
left outer join
(
  select
   max(comment_id) as comment_id,
   user_id
  from
   user_comment
  group by
   user_id
 ) latest on u.user_id = latest.user_id;

Answer 4

DECLARE @Table (ID Int, User_ID, Time DateTime)

-- This gets the latest entry for each user
INSERT INTO @Table (ID, User_ID, Time)
SELECT ID, User_ID, MAX(TIME)
FROM actions z
INNER JOIN users x on x.ID = z.ID
GROUP BY z. userID

-- Join to get resulting action
SELECT z.user_ID, z.Action
FROM actions z 
INNER JOIN @Table x on x.ID = z.ID

Answer 5

This is the greatest-n-per-group problem that comes up frequently on Stack Overflow. Follow the tag for dozens of other posts on this problem.

Here's how to do it in MySQL given your schema with no subqueries and no GROUP BY:

SELECT u.*, a1.*
FROM users u JOIN actions a1 ON (u.id = a1.user_id)
LEFT OUTER JOIN actions a2 ON (u.id = a2.user_id AND a1.time < a2.time)
WHERE a2.id IS NULL;

In other words, show the user with her action such that if we search for another action with the same user and a later time, we find none.

Answer 6

It seems to me that the following will be works

WITH GetMaxTimePerUser (user_id, time) (
    SELECT user_id, MAX(time)
    FROM actions
    GROUP BY user_id
)
SELECT u.name, a.action, amax.time
FROM actions AS a
    INNER JOIN users AS u ON u.id=a.user_id
    INNER JOIN GetMaxTimePerUser AS u_maxtime ON u_maxtime.user_id=u.id
WHERE a.time=u_maxtime.time

Usage of temporary named result set (common table expression or CTE) without subqueries and OUTER JOIN is the way best opened for query optimization. (CTE is something like a VIEW but existing only virtual or inline)