Re: select contiguous addresses that start on a bit boundary

I have a solution.

SELECT start_bit_boundary FROM (
  SELECT
    min(address) as start_bit_boundary,
    status, count(*) as CT
  FROM MAC_addresses
  WHERE     status = 0
  GROUP BY address >> 2
) AS _INNER
WHERE
  _INNER.CT = 4
ORDER BY start_bit_boundary
LIMIT 0,1;

It returns the first of 4 consecutive addresses.
This works with the following data set.

CREATE TABLE addresses ( address BIGINT(20), status INT );
INSERT INTO addresses
VALUES (100000000001,0),
       (100000000003,0),
       (100000000004,0),
       (100000000005,1),
       (100000000006,0),
       (100000000007,0),
       (100000000009,0),
       (100000000010,0),
       (100000000011,0),
       (100000000013,0),
       (100000000008,0),
       (100000000014,0),
       (100000000015,0),
       (100000000016,0),
       (100000000017,0);

If I want to print all the addresses I could do this:

select * from addresses
where status = 0
AND  address BETWEEN (
  SELECT @b := start_bit_boundary FROM (
    SELECT min(address) as start_bit_boundary,status,count(*) as CT
    FROM MAC_addresses
    WHERE status = 0
    GROUP BY address >> 2
  ) AS _INNER
  WHERE _INNER.CT = 4
  ORDER BY start_bit_boundary
  LIMIT 0,1
) AND (@b+3) limit 0,4;

On 12/17/2015 08:14 AM, John Stile wrote:
> I should have said consecutive addresses, rather than contiguous.
> I care about a set of consecutive addresses, and there is no guarantee
> of record order.
>
> On 12/17/2015 07:35 AM, John Stile wrote:
>> I need help creating a select that returns 4 records that have
>> contiguous addresses that start on a bit boundary.
>>
>> If 4 do not exist, I need a return of zero records.
>>
>> I would like to do this in one statement and I do not have ownership of
>> this mysql server, so fancy views, temporary tables, indexing, etc are
>> outside my permission level.
>>
>> I am also not the only consumer of this database, so altering it for my
>> needs could hurt the other consumers.
>>
>> Below I specify the issue and where I am.
>>
>> Thank you for your attention.
>>
>> #
>> # Create problem set
>> # - This has non-contiguous addresses
>> # - This has one status not 0
>> # - This has contiguous addresses that start before the bit boundary
>> #
>> CREATE TABLE addresses ( address BIGINT(20), status INT );
>> INSERT INTO addresses
>> VALUES (100000000001,0),
>>        (100000000003,0),
>>        (100000000004,0),
>>        (100000000005,1),
>>        (100000000006,0),
>>        (100000000007,0),
>>        (100000000008,0),
>>        (100000000009,0),
>>        (100000000010,0),
>>        (100000000011,0),
>>        (100000000013,0),
>>        (100000000014,0),
>>        (100000000015,0),
>>        (100000000016,0),
>>        (100000000017,0);
>> #
>> # This shows the bit boundary, where the start is  (address & 3) = 0
>> #
>> select address, (address & 3) as boundary from addresses where address
>>> 0 and status=0 order by address limit 10  ;
>> +--------------+----------+
>> | address      | boundary |
>> +--------------+----------+
>> | 100000000001 |        1 |
>> | 100000000003 |        3 |
>> | 100000000004 |        0 |
>> | 100000000006 |        2 |
>> | 100000000007 |        3 |
>> | 100000000008 |        0 |
>> | 100000000009 |        1 |
>> | 100000000010 |        2 |
>> | 100000000011 |        3 |
>> | 100000000013 |        1 |
>> +--------------+----------+
>> 10 rows in set (0.00 sec)
>> #
>> # This shows contiguous add, but they do not stat on the bit  boundary
>> #
>> select c1.address, (address & 3) as boundary  from addresses c1 where 4
>> = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
>> BETWEEN c1.address AND (c1.address + 3)  ) limit 10;
>>
>> +--------------+----------+
>> | address      | boundary |
>> +--------------+----------+
>> | 100000000006 |        2 |
>> | 100000000007 |        3 |
>> | 100000000008 |        0 |
>> | 100000000013 |        1 |
>> | 100000000014 |        2 |
>> +--------------+----------+
>> 5 rows in set (0.00 sec)
>>
>>
>>
>> I can't seem to add my ((address & 3) = 0) condition to the correct location to get the desired
>> result. I don't understand how I can use c1.address in the BETWEEN, and
>> yet I can't seem to make ((address & 3) = 0) work anywhere.
>>
>>
>>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql