Mysql Data Hopping Check

I have a Table that looks like this

MAC-ADDRESS | ACCESSPOINT | TIMESTAMP

Data is pushed into the table by all the Accesspoints at 15 minutes intervals.

I want to run a query to tell me the MAC-Addre开发者_运维百科sses that have appeared on more then one Accesspoint withtin a given datim range ?

The query would be like :

Select `MAC_ADDRESS`,`ACCESSPOINT`,`TIMESTAMP`,count(*) as occurences from
theTable where occurences > 1 and `TIMSTAMP` between TIME1 and TIME2 
group by `MAC_ADDRESS` order by occurences desc ;

This would give you list of most repeating mac address followed by lesser ones on same access points .

Try this:

SELECT `MAC-ADDRESS`
  FROM mytable
    WHERE `TIMESTAMP` BETWEEN <YOUR-TIMESTAMP-1> AND <YOUR-TIMESTAMP-2>
    GROUP BY `ACCESSPOINT`
    HAVING COUNT(1) > 1

Try something like...

select mac 
where datim between firstdate and lastdate
group by mac
having count(*) > 1

-- Removed the extra acc form group... JK

SELECT mac_address
     , COUNT(DISTINCT accesspoint) AS occurences
FROM maclist
WHERE `timestamp` BETWEEN @StartTimestamp AND @EndTimestamp
GROUP BY mac_address
HAVING COUNT(DISTINCT accesspoint) > 1

select mac,count( distinct name) as occ from

(Select mac,name from maclist where datim between "2011-07-08 00:00:00" and "2011-07-14 23:59:59" group by name,mac order by mac)

as tmo group by mac having occ>1;

This worked for me