Select DISTINCT and ORDER BY a separate field

[jzader]

In an effort to get a handle on the traffic patterns of the visitors to my web site, I have added code to the top of each page that, upon each page view, inserts into the MySQL database the corresponding IP address, date, and URL for that page view.

So my 'traffic' table has rows in it like this:

165.247.31.17 2004-10-23 17:03:15 /index.php 
165.247.31.17 2004-10-23 17:03:21 /about.php
165.247.31.17 2004-10-23 17:03:45 /faq.php 
70.69.192.196 2004-10-23 17:03:10 /index.php
70.69.192.196 2004-10-23 17:03:28 /faq.php
70.69.192.196 2004-10-23 17:03:59 /join.php

Next, to provide a list of the most recent visitors, I want to select the 25 IP addresses that correspond to the most recent page views. However, I haven't been able to construct a query to perform this (deceptively simple-sounding) task.

Here is the closest I've gotten:

SELECT DISTINCT ipaddress FROM traffic ORDER BY date DESC LIMIT 0, 25

This query gives me 25 distinct IP addresses -- but it shows me IP addresses for the 25 most recent first-time visitors, rather than the 25 most recent visitors. In other words, the IP addresses are ordered by the first date for each IP address (the first time that IP address ever loaded a page at my site), rather than the last date (the last time that IP address loaded a page).

I need a way of controlling which date field (earliest vs. latest) is used to order the corresponding IP addresses, but I've been unable to find any way to do this.

Any suggestions for how to accomplish my goal? Thank you in advance!

[jezzer300]

On a different note, you may want to hold the referer url and agent in that table too. Holding the agent means you could filter out or monitor all the search bots that come to your website.

$_SERVER['HTTP_USER_AGENT']
$_SERVER['HTTP_REFERER']

[jzader]

Thanks, jezzer300. I actually do have fields like that in the table, but I didn't want to complicate my example by talking about them. ;-)

[ergophobe]

Assuming you are not using MySQL 4+ with subqueries and have to do the temp table workaround etc, I think this should work (works with my small amount of test data).

DROP TABLE hold_ip;
CREATE TEMPORARY TABLE hold_ip (`ip` VARCHAR (20));
INSERT INTO hold_ip (ip) SELECT views.ip FROM views ORDER BY views.visit_date DESC;
SELECT DISTINCT hold_ip.ip from hold_ip limit 3;

I'm not sure how safe it is. The problem is that the "distinct" kicks in before the "order by" in your original version. This solution depends on the ORDER BY in the INSERT... SELECT putting the data into the table in a given order so that the first ip is also the most recent for that particular ip. That would make me more than a little nervous if this was important at all.

If you have a more recent version, you can likely find a more solid solution using subqueries.

Tom