[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