Re: How to write the following query ?
Hi
The table I use is:
MacAddress (BigInt)
Owner (Varchar(45))
I tried to run the query with the following table content:
MacAddress | Owner
-------------------------
261683767410688, 'amit'
261683767410689, 'amit'
261683767410690, ''
261683767410691, 'avi'
261683767410692, ''
261683767410693, ''
261683767410694, ''
261683767410695, 'alon'
261683767410696, 'noam'
261683767410697, ''
261683767410698, ''
261683767410699, ''
261683767410700, ''
261683767410701, 'Tom'
261683767410702, 'Tom'
261683767410703, 'Tom'
261683767410704, 'Tom'
261683767410705, 'Tom'
261683767410706, ''
261683767410707, ''
I run this query: (as you answer me, with X=4)
select a.macaddress - 3
from mactable a
join mactable b
on a.macaddress between b.macaddress and b.macaddress + 4
where a.Owner = ''
and b.Owner = ''
group by a.macaddress
having count(*) >= 4;
And I get the following results:
261683767410691
261683767410697
The result: 261683767410691 does not correct,
Can you please tell me how to fix the query to get the correct result ?