How To Get The Number Interval Between Rows via MySQL

Question

We have a table which has two columns -- ID and Value. The ID is the index of table row, and the Value consists of Fixed String and Key (a number) in hexadecimal storing as string in the database. Take 00001810010 as an example, the fixed string is 0000181 and the seconds part is the key -- 0010.

Table

ID       Value        
 0      00001810000     
 1      00001810010    
 2      00001810500    
 3      00001810900     
 4      0000181090a

What I want to get from the above table is the Number Interval between rows, for above table the result is
[1, 9], [11, 4FF], [501, 8FF], [901, 909]

I can read all the records into memory and handle them via C++, but is it possible to implement it through MySQL statements only? How?

If it was me, I'd break this down into 3 separate problems. 1. How to get the appropriate substring? 2. How to work with hexadecimal numbers? 3. How to find gaps in a sequence? — Strawberry, Apr 22 '14 at 13:18

score 0 · Answer 1 · answered Apr 22 '14 at 14:59

I would be tempted to match up a row with the previous row with something like this:-

SELECT sub1.id AS this_row_id,
        sub1.value AS this_row_value,
        z.id AS prev_row_id,
        z.value AS prev_row_value
FROM 
(
    SELECT a.id, a.value, MAX(b.id) AS bid
    FROM some_table a
    INNER JOIN some_table b
    ON a.id > b.id
    GROUP BY a.id, a.value
) sub1
INNER JOIN some_table z
ON z.id = sub1.bid

You might want to use LEFT OUTER JOINs rather than INNER JOINs depending on what you want for the first record (where there is no previous record to match on).

How To Get The Number Interval Between Rows via MySQL

1 Answers1