[thelist] Most recent rows from a table

[Bill Moseley]

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