[thelist] Most recent rows from a table
Matt Warden
mwarden at gmail.com
Fri Mar 6 15:22:31 CST 2009
On Fri, Mar 6, 2009 at 3:39 PM, Bill Moseley <moseley at hank.org> wrote:
> 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.
Try something like this:
select *
from user,
(select *, max(event_time) as max_event_time
from user_event
group by user_id) as uevent
where user.id=uevent.user_id
and event_time=uevent.max_event_time
--
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com
This email proudly and graciously contributes to entropy.
More information about the thelist
mailing list