On Thu, 4 Nov 2010 08:07:02 -0300 Paul Halliday <paul.halli...@gmail.com> 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 <sim...@syounger.com> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
