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

[SirAndy]

well, at least i got the list ordered by time now ...

still not pretty, looping queries and all, but it works, i guess ...
(IMG:style_emoticons/default/dry.gif) Andy

CODE

SELECT iUserID, max(dtCreated) as dtMax FROM tbl_EventLog
GROUP BY iUserID
ORDER BY dtMax DESC

this gives me a initial resultset with all the user ids sorted by date. i now can loop through all of these and find the latest entries for the day ...

[blitZ]

Hmm, wouldn't something like this work?


SELECT *
FROM tbl_EventLog
WHERE iID IN (

select iID from (
SELECT iUserID, iID, max(dtCreated) as lastEntry
FROM tbl_EventLog
WHERE dtCreated >= '3/26/2007 0:00:01 AM'
group by iUserID, iID ) dTable )

order by dtCreated

[SirAndy]

Hmm, wouldn't something like this work?


SELECT *
FROM tbl_EventLog
WHERE iID IN (

select iID from (
SELECT iUserID, iID, max(dtCreated) as lastEntry
FROM tbl_EventLog
WHERE dtCreated >= '3/26/2007 0:00:01 AM'
group by iUserID, iID ) dTable )

order by dtCreated

no suck luck ... (IMG:style_emoticons/default/sad.gif)
tried it, returns the full set of 4000+ entries in the eventlog for today.

keep 'em coming ...
(IMG:style_emoticons/default/type.gif) Andy