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

[Paul Cowan]

Bart wrote:
> I want to return only the
> most recent record for each event_date / username combo.

Try this on for size.

Assumption: event_date/username/date_modified is a unique combination.
If not, you'll need further work on this.

I will also assume MS SQL Server. Adjust to fit.

SELECT
    *
FROM
    mytable
INNER JOIN
    (SELECT event_date, username, max(date_modified) as MostRecentDate
     FROM mytable) AS mytable2 ON
    (mytable.event_date = mytable2.event_date) AND
    (mytable.username = mytable2.username) AND
    (mytable.date_modified = mytable2.mostrecentdate)

This should work, if not it's because I'm too lazy to double-check my
syntax.

One last assumption: you're going to replace the "SELECT *" with a proper
field list. If not, you should be!

Cheers,

Paul