Advance SQL Statement Help

I have all my website access statics logging data into a SQL table with
the following structure:
id int identity
ip nvarchar 23
referer nvarchar 512
request nvarchar 512
website nvarchar 15
bytes int
process_time int
access_time datetime
Each time a page is loaded the values are logged. So if a single user
navigates 20 pages, there are 20 records in the database.
What I want to do is generate a sql statement that will return me all
the accesses to a specific website on a given day, grouped by the ip
address and sorted by the access_time.
Ideally it would return the ip addresses in date order based on their
first entry, with the responses per ip in their date order. Therefore
if
IP 216.113.235.52 had three hits at:
12:15:29
12:15:54
12:16:03
IP 216.113.214.190 had three hits at:
12:15:25
12:15:31
12:15:48
It would return a result set like:
216.113.214.190 @. 12:15:25
216.113.214.190 @. 12:15:31
216.113.214.190 @. 12:15:48
216.113.235.52 @. 12:15:29
216.113.235.52 @. 12:15:54
216.113.235.52 @. 12:16:03
What I'm doing now must not be very efficient as it takes several
seconds to return just a small list of data (roughly 3 seconds to
return 50 or so hits).
Currently I use two queries:
Query 1:
SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
<end_date> AND website LIKE '%<website>%' GROUP BY ip
OR
SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
<= <end_date> AND website LIKE '%<website>%'
Either of these gives me a unique list of ips on the given day
(unfortunately they're not sorted in date order :^( )
Then with this list of unique ips, I perform a second query, looping
through the ip addresses from the first query:
SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
This gives me the users path through the website in date order.
My problems are that:
1. The things just too slow.
2. I don't have a sorted list (the first user of the day may not
necessarily be the first listed).
Is it possible to generate a single query that will return the desired
results in order?
FWIW I'm accessing the database through JDBC.
Thanks in advance.SELECT website, ip, access_time
FROM access_log
WHERE
access_time >= '20060207 00:00:00.000'
AND access_time <= '20060208 00:00:00.000'
ORDER BY website, ip, accesstime ASC
This produces a listing of websites that were access by ip's, ordered by the
access_time. If you added the request column to this query, it would also
show you the path that each ip took through the website.
I've been doing a lot of work with analyzing web access logs lately. Let me
know if this was what you were looking for; if not I'll see what else I can
come up with.
"Tom Cole" wrote:

> I have all my website access statics logging data into a SQL table with
> the following structure:
> id int identity
> ip nvarchar 23
> referer nvarchar 512
> request nvarchar 512
> website nvarchar 15
> bytes int
> process_time int
> access_time datetime
> Each time a page is loaded the values are logged. So if a single user
> navigates 20 pages, there are 20 records in the database.
> What I want to do is generate a sql statement that will return me all
> the accesses to a specific website on a given day, grouped by the ip
> address and sorted by the access_time.
> Ideally it would return the ip addresses in date order based on their
> first entry, with the responses per ip in their date order. Therefore
> if
> IP 216.113.235.52 had three hits at:
> 12:15:29
> 12:15:54
> 12:16:03
> IP 216.113.214.190 had three hits at:
> 12:15:25
> 12:15:31
> 12:15:48
> It would return a result set like:
> 216.113.214.190 @. 12:15:25
> 216.113.214.190 @. 12:15:31
> 216.113.214.190 @. 12:15:48
> 216.113.235.52 @. 12:15:29
> 216.113.235.52 @. 12:15:54
> 216.113.235.52 @. 12:16:03
> What I'm doing now must not be very efficient as it takes several
> seconds to return just a small list of data (roughly 3 seconds to
> return 50 or so hits).
> Currently I use two queries:
> Query 1:
> SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
> <end_date> AND website LIKE '%<website>%' GROUP BY ip
> OR
> SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
> <= <end_date> AND website LIKE '%<website>%'
> Either of these gives me a unique list of ips on the given day
> (unfortunately they're not sorted in date order :^( )
> Then with this list of unique ips, I perform a second query, looping
> through the ip addresses from the first query:
> SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
> This gives me the users path through the website in date order.
> My problems are that:
> 1. The things just too slow.
> 2. I don't have a sorted list (the first user of the day may not
> necessarily be the first listed).
> Is it possible to generate a single query that will return the desired
> results in order?
> FWIW I'm accessing the database through JDBC.
> Thanks in advance.
>