List:General Discussion« Previous MessageNext Message »
From:Roy Lyseng Date:February 17 2014 6:17pm
Subject:Re: Narrowing a SELECT statement by multiple hits
View as plain text  
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
>
>

Thread
Narrowing a SELECT statement by multiple hitsJennifer13 Feb 2014
  • Re: Narrowing a SELECT statement by multiple hitsLarry Martell13 Feb 2014
    • Re: Narrowing a SELECT statement by multiple hitsJennifer17 Feb 2014
      • Re: Narrowing a SELECT statement by multiple hitsRoy Lyseng17 Feb 2014
        • Re: Narrowing a SELECT statement by multiple hitsJennifer17 Feb 2014
          • Re: Narrowing a SELECT statement by multiple hitsRoy Lyseng17 Feb 2014
            • Re: Narrowing a SELECT statement by multiple hitsJennifer18 Feb 2014
          • Re: Narrowing a SELECT statement by multiple hitsshawn l.green17 Feb 2014