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

[SirAndy]

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:

hmmm, i thought about using a temp table as well. that might work ... (IMG:style_emoticons/default/idea.gif)

too bad Microsoft never got over the fact that "DISTINCT ON" is not part of the SQL standard.
"DISTINCT ON" let's you specify multiple columns to return but the distinct clause is only used on the column you specify with the ON param ...

really too bad MS does not support it. but many others don't either. would have been nice to have, as it would solve my problem and let me get away with just one simple query ...

for now, i'm doing to two nested queries in the code. it works fine and speed is not an issue here as it is in the admin section of the B2B site.
the page only gets maybe 30 hits a day ...

thanks for all the input guys!
(IMG:style_emoticons/default/pray.gif) Andy