WOT: SQL query help needed again ... Options

[SirAndy]

it's time for another SQL question ...

here's the problem:

i have a table with event log information. it has a unique id for each entry (primary key), a user id, timestamp and other info.

getting the info out of the table is easy except for one particular way.

what i need is a page where i list all the last actions taken by any user per day.
meaning, if users xyz created 30 logentries today, i only want to show the latest for any given day.
so, the list should contain exactly ONE entry for each distinct user per day, showing his/her latest action ...

something like this, except, the query below doesn't work ... (IMG:style_emoticons/default/rolleyes.gif)

CODE

SELECT * FROM tbl_EventLog WHERE iID IN (

  SELECT DISTINCT el.iUserID, dummy.iID FROM tbl_EventLog as el
  INNER JOIN tbl_EventLog as dummy ON dummy.iUserID = el.iUserID
  WHERE (el.dtCreated >= '3/26/2007 0:00:01 AM')

)

anyone?
(IMG:style_emoticons/default/confused24.gif) Andy

[bigkensteele]

I have kicked this around for a half hour, and I think that the most efficient way to do it would be a to create a work table using something like this:

SELECT xxx.user, xxx.date, Max(xxx.time) AS MaxTime INTO WORKTABLE
FROM xxx
GROUP BY xxx.user, xxx.date
HAVING xxx.date = 03/26/2007;

Then do a join of table xxx and WORKTABLE using user, date, and time = MaxTime.

SELECT xxx.*
FROM WORKTABLE INNER JOIN xxx ON (WORKTABLE.user = xxx.user)
and (WORKTABLE.date = xxx.date)
and (WORKTABLE.MaxTime = xxx.time);

With the above solution, I would create an additional index on your table over the user, date and time columns.

However, if your primary key is a sequentially assigned numeric, you could include that in the first query as

SELECT Last(xxx.key) AS LastKey, xxx.user, xxx.date, Max(xxx.time) AS MaxTime INTO WORKTABLE
FROM xxx
GROUP BY xxx.user, xxx.date
HAVING xxx.date = 03/26/2007;

and then your second query would only need to be

SELECT xxx.*
FROM WORKTABLE INNER JOIN xxx
ON (WORKTABLE.LastKey = xxx.key);

Hope this helps, please let us know what you end up doing.
BKS