Hi Jennifer,
please try filtering with a subquery that locates ip addresses with more than 1
attempt:
SELECT ip, page, url, time_stamp
FROM ip_adresses
WHERE <existing where clause> AND
ip IN (SELECT ip
FROM ip_addresses
WHERE <existing where clause>
GROUP BY ip
HAVING COUNT(*) > 1
)
I think this will be more performant on version 5.6, though. If this is too
slow, you may try materializing the subquery in a temporary table and use that
table instead of the subquery.
Thanks,
Roy
On 17.02.14 17:42, Jennifer wrote:
On Feb 12, 2014, at 6:30 PM, Larry Martell wrote:
Try adding a having clause, e.g.:
SELECT `ip`,`page`,`url`,`time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
HAVING COUNT(ip) >1
ORDER BY INET_ATON(`ip`), `time_stamp`
Thank you Larry for the response. Unfortunately, I can't get it to
work. The code above only returns one row. It should return 35 rows. If I
remove the HAVING COUNT line, 52 rows are returned.
If I add COUNT(`ip`) AS UniqueIPs to the SELECT line, that shows that
there are 52 records, but still only returns one row. So I added GROUP BY `ip`
and that gave me 7 rows with counts that added up to 35. Closer, but each row
was a group of IP addresses where there was more than one hit.
I want each hit to be returned, not a "summary" of hits per IP, so I
don't think GROUP BY is what I need(?). I've run across a couple of sites that seem to
say that an INNER JOIN would give me what I want. If that's true, then that's above my
head.
BTW, this on MySQL 5.5.34-cll-lve
Thank you,
Jenni
Superior Shelving Systems::::....
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984
Wire LAN Shelving:
http://www.superiorshelving.com/mfg/nexel/pages/wire-shelving-chrome.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
