[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:

            event_type = 'logged in'
        ORDER BY
            event_time DESC
        ) AS event_time

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?


Bill Moseley
moseley at hank.org
Sent from my iMutt

More information about the thelist mailing list