[thelist] SQL - Getting most recent record of a combo

[rudy]

> Then I would programmatically loop thru all of those

hi bart

no, don't do that   ;o)

> Is there a way to do this in a single query?  I'm thinking I need to do a
> GROUP BY but I'm a little fuzzy on how to structure it.

i find a correlated subquery has syntax that's easier to understand

(there is also a way to do it with a join to a grouped derived table)

it works like this:

the table has multiple records for each event_date / username

you want only one of of them, the latest, for each event_date / username

you want the row that has the date modified that is the latest out of of all
the rows that have the same date_time / username as the row you're looking
at

select event_date, username, date_modified
  from yourtable X
 where date_modified =
       ( select max(date_modified)
             from yourtable
           where event_date = X.event_date
               and username = X.username )

X is a correlation variable, required here because it's the same tablename
in the subquery as the main query, and you need to distinguish columns

a correlated subquery is, in effect, a self-join with grouping

oh boy i hope that made some kind of sense


rudy