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
