CODEWITHSUNDEEP
mysqlsqlalgorithm
anthozep
anthozep

Reputation: 333

Find contiguous index until certain field changes

I have some data in a MySQL server that has an index number from 0 to 15, and two other fields that contain a name and an address.

Name    Address Index
-----   ------- --
Test    0x0100  0
Test    0x0100  1
Test    0x0100  2
Test    0x0100  3
Test    0x0100  4
Test2   0x0100  5
Test2   0x0100  6
Test2   0x0100  7
Test    0x0100  8
Test    0x0100  9
Test    0x0100  10
Test3   0x0100  11
Test3   0x0100  12
Test    0x0100  13
Test    0x0100  14
Test    0x0100  15

The data is sorted by address and then index.

I would like to summarize this data with the range of indices when each index, like the following:

Name    Address Start   End
-----   ------- -----   ----
Test    0x0100  0       4
Test2   0x0100  5       7
Test    0x0100  8       10
Test3   0x0100  11      12
Test    0x0100  13      15

Is there a way to do this in MySQL? Alternatively, Is there an efficient algorithm to accomplish this other than brute force? Some post processing is possible after the query returns.

Upvotes: 1

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do this in MySQL, using variables:

select name, address, min(index) as start_index, max(index) as end_index
from (select t.*,
             (@grp := if(@name = name, @grp,
                        if(@name := name, @grp + 1, @grp + 1)
                       )
             ) as grp
      from t cross join
           (select @grp := 0, @name := '') params
      order by index
     ) t
group by name, address, grp;

Here is a working version.

Upvotes: 2

Related Questions