Missing results.

I can't wrap my head around this one..

I have this query:

SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
INET_NTOA(src_ip), src.cc, INET_NTOA(dst_ip), dst.cc FROM event LEFT
JOIN mappings AS src ON event.src_ip = src.ip LEFT JOIN mappings AS
dst ON event.dst_ip = dst.ip WHERE timestamp BETWEEN '2010-11-03
13:00:00' AND '2010-11-03 14:00:00' AND (signature LIKE '%ET POLICY
facebook apps activity%') AND (INET_NTOA(src_ip) LIKE '10.13.11.29' OR
INET_NTOA(dst_ip) LIKE '10.13.11.29') GROUP BY src_ip, src.cc, dst_ip,
dst.cc ORDER BY maxTime DESC;

which returns something like:

1 2010-11-03 13:41:50
380 2010-11-03 13:41:46 <-
24 2010-11-03 13:22:39 <-
1 2010-11-03 13:19:20 <-
1 2010-11-03 13:19:20
1 2010-11-03 13:18:37

Now, if I add 6 hours to the end timestamp (the only change I made to
the query) it returns this:

30 2010-11-03 19:58:55
209 2010-11-03 19:40:00
95 2010-11-03 19:28:28
1 2010-11-03 19:04:30
5 2010-11-03 19:02:19
8 2010-11-03 19:02:19
4 2010-11-03 19:02:18
2 2010-11-03 18:44:02
765 2010-11-03 18:21:44
5 2010-11-03 18:09:22
2 2010-11-03 18:09:22
1 2010-11-03 17:50:19
1 2010-11-03 17:33:39
1 2010-11-03 15:54:57
5 2010-11-03 15:02:14
1 2010-11-03 15:02:00
2 2010-11-03 14:50:19
1 2010-11-03 13:41:50
1 2010-11-03 13:19:20
1 2010-11-03 13:18:37


Where did 13:41:46, 13:19:20 and 13:22:39 go?

Thanks!
--
Paul Halliday
Ideation | Individualization | Learner | Achiever | Analytical
http://www.pintumbler.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing results.

On Thu, 4 Nov 2010 08:07:02 -0300
Paul Halliday wrote:


> SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
> INET_NTOA(src_ip), src.cc, INET_NTOA(dst_ip), dst.cc FROM event LEFT
> JOIN mappings AS src ON event.src_ip = src.ip LEFT JOIN mappings AS
> dst ON event.dst_ip = dst.ip WHERE timestamp BETWEEN '2010-11-03
> 13:00:00' AND '2010-11-03 14:00:00' AND (signature LIKE '%ET POLICY
> facebook apps activity%') AND (INET_NTOA(src_ip) LIKE '10.13.11.29' OR
> INET_NTOA(dst_ip) LIKE '10.13.11.29') GROUP BY src_ip, src.cc, dst_ip,
> dst.cc ORDER BY maxTime DESC;
>
> which returns something like:
>
> 1 2010-11-03 13:41:50
> 380 2010-11-03 13:41:46 <-
> 24 2010-11-03 13:22:39 <-
> 1 2010-11-03 13:19:20 <-
> 1 2010-11-03 13:19:20
> 1 2010-11-03 13:18:37
>
> Now, if I add 6 hours to the end timestamp (the only change I made to
> the query) it returns this:
>
> 30 2010-11-03 19:58:55
.....
> 1 2010-11-03 13:41:50
> 1 2010-11-03 13:19:20
> 1 2010-11-03 13:18:37
>


Presumably those records were absorbed into your 'group by' clause, since there was an entry, from a later time, which had the same values for all the group by fields.


--
Simcha Younger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing results.

> Presumably those records were absorbed into your 'group by' clause, since there was an entry, from a later time, which had the same values for all the group by fields.
>
>
> --
> Simcha Younger
>

Geez, how obvious. I was thinking on a completely different plane. I
feel pretty silly now. :)

Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org