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

[blitZ]

The Max(dtCreated ) would probably work, then group by user and log ID. You will then have the IDs you need to get the details you require.

[SirAndy]

The Max(dtCreated ) would probably work, then group by user and log ID. You will then have the IDs you need to get the details you require.

that's what i was trying to avoid ... i hate looped queries. i'm looking for a single query that will give me a single resultset to work with.

for now, i have created a nested loop (yuk (IMG:style_emoticons/default/barf.gif) ) and that works, but it seems like there should be a better way ...

here's what i have right now (works, but is ugly (IMG:style_emoticons/default/icon8.gif) )

CODE

query1 =
SELECT DISTINCT iUserID FROM tbl_EventLog
WHERE dtCreated >= '03/26/2007 00:00:01 AM'
ORDER BY iUserID

do while not query1.EOF

query2 =
SELECT TOP 1 el.*, iut.sName as sType FROM tbl_EventLog as el    
INNER JOIN tbl_INFO_UserTypes as iut ON iut.iID = el.iUserType
WHERE (el.iUserID = query1.iUserID) AND (el.dtCreated >= '03/26/2007 00:00:01 AM')
ORDER BY el.dtCreated DESC

... do something here with result ...


query1.MoveNext
loop

anything better would be good. anyone?
(IMG:style_emoticons/default/pray.gif) Andy