A Structured Query Language (SQL) challenge

robblack

New member
Local time
Today, 21:57
Joined
Nov 2, 2006
Messages
8
All,

I'm working on a bespoke logging feature for a web portal which records user events in a single table (tblUserLog as illustrated below).

id session_id auth_user app_name event remote_addr http_user_agent timestamp
233 1063924932 jim App 1 Some event 10.203.115.30 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322) 22-Nov-06
234 1063924934 joe App 2 some other event 10.203.112.40 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322) 22-Nov-06
235 1063924934 joe App 1 Some event 10.203.112.40 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322) 22-Nov-06
236 1063924934 joe App 1 Some event 10.203.112.40 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322) 22-Nov-06
237 1063924936 jim App 2 some other event 10.203.112.40 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322) 22-Nov-06
238 1063924936 jim App 2 some other event 10.203.112.40 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322) 22-Nov-06
239 1063924936 jim App 2 A completely different event 10.203.112.40 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322) 22-Nov-06


To supplement this flat list I'm preparing a number of key stats which will be reported to administrators (e.g. unique user/session count by period, app usage by period etc).

I'm now looking to report the (10?) most active users where most active means those users having the most unique sessions. The desired results are as follows:

User Sessions
joe 23
paul 10
chris 5
jim 2
... ...

Within Access its relatively easy to create/combine a number of queries to acheive this but I need to do this via an ASP script. The challenge is to design a single SQL statement (with nested SQL?) which will return a recordset as above.

Any help on this would be much appreciated.



Thanks in advance,


Rob.
 
Something like the following would be good but It doesn't appear to work.

SELECT auth_user, count(session_id) as sessioncount
FROM
( SELECT DISTINCT auth_user, session_id from tblUserLog ) AS X
GROUP BY auth_user
ORDER BY count(session_id) DESC
 
As it turns out, the above SQL executed in ADODB/JET when called via an ASP script. However, when there is a need to nest SQL SELECT queries (even for the purpose of visulising a recordset) it seems the Access 97 query/SQL editor isn't up to the job.

Thanks anyway.


Rob.
 

Users who are viewing this thread

Back
Top Bottom