mySQL (left join?)

Question

I have 2 tables which I want to query from.

The first table (lets call it users) has these columns: id and name.

The second table (lets call it transactions) has these columns: user_id, amount and timestamp.

How do I find out the latest timestamp in transactions for each user in the users table?

EDIT:

Thanks for all your answers so far! Really appreciate it. How about, if I want to select users with the latest timestamp greater than a certain value?

This looks similar: http://stackoverflow.com/questions/5820042/max-date-for-each-worker — Sudhir Bastakoti, Jun 25 '12 at 04:04

score 3 · Accepted Answer · answered Jun 25 '12 at 04:05

3

SELECT u.*,MAX(t.timestamp) latest_timestamp
FROM transactions t
LEFT JOIN users u ON t.user_id=u.id
GROUP BY t.user_id

Should be what you're looking for. This query should select the latest transaction timestamp for each user.

answered Jun 25 '12 at 04:05

Sean Johnson

5,567
2
17
22

Except this won't work, since all of the columns in users are not specified in the GROUP BY clause. :( – Stuart Ainsworth Jun 25 '12 at 04:25
I'm assuming that each user has a unique user id. It may even be possible for different users to have the same name, so I didn't include that field in my group by. – Sean Johnson Jun 25 '12 at 04:27
Thanks, this worked. (: Any idea why when I add `WHILE latest_timestamp > xxxxxxxxx`, it doesn't return the expected rows? – mushroom Jun 25 '12 at 04:33
One other thing, my query won't include users who have no transactions. Did you need those users included in the result set? To answer your question, it's WHERE, not WHILE. `... WHERE latest_timestamp > xxxxx` – Sean Johnson Jun 25 '12 at 04:35
Nop, I don't need users who have no transactions. Oh yep, and I meant `WHERE` but it doesn't work that way too. – mushroom Jun 25 '12 at 04:37
1

Oh, actually since the filter relies on the aggregate function of the GROUP BY clause, you need to use HAVING rather than WHERE. `... HAVING latest_timestamp>xxxxxx` – Sean Johnson Jun 25 '12 at 04:39
@StuartAinsworth actually, in mysql, you can have columns that arent in group by or aggregate functions. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html it just uses any value – Jarry Jun 25 '12 at 16:35

score 1 · Answer 2 · answered Jun 25 '12 at 04:07

1

SELECT 
     id,
     name,
     max(timestamp) 
FROM users 
INNER JOIN transactions 
ON users.id = transactions.user_id
GROUP BY id,name

that should give you te greatest timestamp for every user in the users table

answered Jun 25 '12 at 04:07

Jarry

1,891
3
15
27

score 0 · Answer 3 · answered Jun 25 '12 at 04:08

0

Try this

SELECT Users.name,MAX(T.timestamp) As LastTimeStamp
FROM Users 
LEFT JOIN Transactions as T ON Users.user_id=T.user_id    
GROUP BY Users.name

answered Jun 25 '12 at 04:08

Sudhakar B

1,465
9
16

score 0 · Answer 4 · answered Jun 25 '12 at 04:13

This query will do more than what you asked, but this is just in case your next question would be "what if I also wanted to know the amount that belongs to the maximum timestamp?":

SELECT id, name, t.`timestamp`, amount
FROM (SELECT id, name, MAX(`timestamp`) AS `timestamp`
    FROM users u
    INNER JOIN transactions t ON t.user_id=u.id
    GROUP BY id, name
) AS maxt
INNER JOIN transactions t 
ON t.user_id = maxt.id AND t.`timestamp` = maxt.`timestamp`

mySQL (left join?)

4 Answers4