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 ...




anyone?
Andy

Can you use a MAX clause on the date/time stamp? That would probably eliminate the need for the DISTINCT clause.

i don't see how using MAX on the date/timestamp would yield a resultset with only one entry per unique user ...
Andy

He's right - we have to do something similar in our software - you do max(date) where date is less than the end of the day you're interested in.

I think you might group by userid - but not sure....

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 ) and that works, but it seems like there should be a better way ...

here's what i have right now (works, but is ugly )




anything better would be good. anyone?
Andy

Not sure what DB you are using, but any thoughts on dropping that into a stored proc?


that way you can abstract the looping to the DB layer and return a single result set. Performance would probably be better as well.

yeah, i could. MS-SQL 8 Server.
problem is, the server app is HUGE and was entirely done in ASP without a single stored procedure. it's all inline ASP queries.

if i start using stored procedures now, i'm afraid i'll confuse all the other programmers even more than they already are.
we're talking hundreds of thousands of lines of code ...
Andy

plus, i just noticed, with my hacked solution, i can't sort the result by date ...

Andy

That's a really good point. A lot of the legacy ASP code where I work is a mix of overly complex stored procs and inline queries. Its a mess for others to figure out (READ: me the last 2 months).


Do we have any true DBAs in the club or just programmers who dabble in DB?

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

no real DBA's here ... this is just a project i'm doing working as a contractor. they brought me in for the project because nobody here inhouse can handle the scope ...

lots of work but good money and otherwise easy. i do easier SQL all day long, it's the more complicated queries where my lack of edufication shows.

should have taken those damm classes 20 years ago. went drinking instead ...
Andy

I am the same way....I know enough DB/SQL to be dangerous but its not my strength by a long shot.

I can ask one guy I know tomorrow who might have a nifty solution for this....

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 ...
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):

Click to view attachment

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

still not pretty, looping queries and all, but it works, i guess ...
Andy



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 ...

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 ...
tried it, returns the full set of 4000+ entries in the eventlog for today.

keep 'em coming ...
Andy

Andy,
Look up a tool on Microsoft's website called "eventcombMT". It will parse the event logs on any server, and return them by date, type and user, or any other field. It also doesn't require a table be imported to SQL, just query them live. Embed the app in a stored procedure, and feed it the proper switches.

this is *NOT* a windows event log !!!

it's just a DB table called tbl_EventLog and has absolutely NOTHING to do with the OS eventlog!

it logs events of peoples (users) interaction with the B2B website ...
Andy

PS: it's *NOT* a webserver log either! this is a completely proprietary format used by the B2B website to track specific events on the user pages.
it's populated by the ASP code and i need to build a page in the admin section that allows to break down the log by user/date ...

OK...OK... I am suitably chastised... I thought you were pulling in the windows event log and then searching it with a SQL query.

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

good response...avoiding IN clauses is nice because they are pretty expensive.

hmmm, i thought about using a temp table as well. that might work ...

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!
Andy