[thelist] Most recent rows from a table
Bill Moseley
moseley at hank.org
Fri Mar 6 14:39:44 CST 2009
It must be Friday.
This is a very simple task that's escaping me right now.
I have a table that logs user events. Each event has a number of
attributes and is associated with a specific user. It's a one-to-many
relationship, of course.
What I need is to join the user table with the logging table but for
each user I only want the most recent log entry of a given type. That
is, users are only listed once.
So, if an event is "logged in" and I want a list of their most recent
login time:
SELECT
user.id,
(SELECT
event_time
FROM
user_events
WHERE
event_type = 'logged in'
ORDER BY
event_time DESC
LIMIT
1
) AS event_time
FROM
user;
But, I really want to join all the columns from both tables, not just
one column from the log table. And doesn't seem like a subquery for
each column makes sense.
Pretty common to have a one-to-many situation where I want to join
the two but have the "one" distinct and join with the "many" side by
some criteria that makes it one-to-one.
Then this final table I'll likely join with other tables.
Can someone point me in the right direction?
Thanks,
--
Bill Moseley
moseley at hank.org
Sent from my iMutt
More information about the thelist
mailing list