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

[watsonrx13]

Andy, what's the fields that you're trying to return?

If you only wanted the userid and date/time then....

Select MAX(date/time),userid
from xxxx
where date/time between fromdate and todate
group by userid;

BTW, I'm a PL/SQL Oracle developer....

-- Rob

[SirAndy]

Andy, what's the fields that you're trying to return?

If you only wanted the userid and date/time then....

Select MAX(date/time),userid
from xxxx
where date/time between fromdate and todate
group by userid;

BTW, I'm a PL/SQL Oracle developer....

-- Rob

yeah, thought of that but won't work. i need all the columns in the table for each result.
the problem with "GROUP BY" as well as "DESTINCT" is that you can't have any other fields returned unless you add them to the "GROUP BY".
which renders the query useless ...
(IMG:style_emoticons/default/headbang.gif) Andy

PS: what i need:

- go through all rows in table tbl_EventLog
- find all entries from today
- narrow down to last entry for each user
- sort by time (DESC)

each user has many entries in the event log every day, but the list should only show their latest activity ...

the output will look like this (note that the list *should* be sorted by time, but it isn't right now):