SQL logic

[BigBadDave]

I am trying to get a handle on selecting the top 10 entry and exit pages of my apache access_log database. I log the url, host name etc. This is what I have for top 10 popular URL's if its any help:

SELECT `url`, COUNT(`url`) AS `num` FROM `access_log` WHERE `date` = '0000-00-00' GROUP BY `url` ORDER BY `num` DESC LIMIT 0, 10;

If anyone can point me in the right direction I'd appreciate it.

Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com

 

[r937]

if you could explain which rows represent entry pages and which rows represent exit pages, you would just modify the query you have, except change the WHERE condition

rudy

http://r937.com/

SQL Consulting

 

[BigBadDave]

It's not that simple unfortunately, this is what I have but I don't think its accurate:

CREATE TEMPORARY TABLE `tmp` SELECT `url`, COUNT(DISTINCT `host name`) AS `num` FROM `access_log` WHERE `date` = '0000-00-00' GROUP BY `host name` ORDER BY `num` DESC;
SELECT `url`, SUM(`num`) AS `num` FROM `tmp` GROUP BY `url` ORDER BY `num` DESC LIMIT 0, 10;

Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com

 

[r937]

yeah, but how do you tell entry pages from exit pages?

rudy

http://r937.com/

SQL Consulting

 

[BigBadDave]

That's what I need to do, I assume I can do it by selecting the time field and seeing the last request by host name within a 20 minute period for example but I'm not sure.

Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com