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.

Link: Find contiguous index until certain field changes
Source: Stack Sql


About Author

Leave A Reply