List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:February 17 2014 10:20pm
Subject:Re: Narrowing a SELECT statement by multiple hits
View as plain text  
Hi Jennifer,

On 2/17/2014 3:11 PM, Jennifer wrote:
> CREATE TEMPORARY TABLE temp_ip AS
> (SELECT `ip`
>      FROM `ip_addresses`
>      WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
> CURDATE() - INTERVAL 1 SECOND)
> AND TRIM(`referrer`) LIKE ''
>      GROUP BY `ip`
>      HAVING COUNT(*) > 1
>     );
> 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 '' AND
> `ip` IN (temp_ip)
> ORDER BY INET_ATON(`ip`), `time_stamp`;

You are *so* close!

CREATE TEMPORARY TABLE temp_ip AS
SELECT `ip`
     FROM `ip_addresses`
     WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) 
AND CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
     GROUP BY `ip`
     HAVING COUNT(*) > 1
;

ALTER TABLE temp_ip ADD KEY(ip);

SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses`
INNER JOIN tmp_ip
   ON ip_addresses.ip = tmp_ip.ip
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
ORDER BY INET_ATON(`ip`), `time_stamp`;

Give that a whirl and let us know your results.
-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
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