Complex mysql query with variable conditions

See more:

SQL

MySQL

Hey guys,

Background
I am working on making one of our database MUCH more efficient. The largest table has 3M rows with some text columns containing one of two values >_<

So I have taken out the text, performed some normalization and de-normalization steps, added plenty of indexes where required and it has made it about 3K% to 6K% more efficient. Woot. "Quick quite" is no longer a misnomer, Bosses all impressed, customers very happy etc...

So now they want me to take it another step >_<

Anyway, enough background. Here's the problem

Problem

All of our products have an EAC range (fromcon and tocon). Based on several other criteria, I need to find where these are overlapping or adjacent so I can reduce the total number of records.

I.E:

TableId, FromCon, ToCon, OtherCrieria
1      , 0      , 100  , 1
2      , 101    , 200  , 1
3      , 200    , 400  , 1
4      , 401    , 600  , 2
5      , 601    , 800  , 1

So above there are the first three rows that are adjacent or overlap with the same criteria, another row that is adjacent but the criteria don't match, and the last row with matching criteria but the range is not adjacent to the other matches. The results should look as below:

TableId, FromCon, ToCon, OtherCrieria
1 (min), 0      , 400  , 1
4      , 401    , 600  , 2
5      , 601    , 800  , 1

So how could I go about doing something like this?

Thanks
Andy

What I have tried:

Not even sure where to start so here is some test data instead

select * from (
	select 1 as id, 0 as fromcon, 100 as tocon, 1 as conditions
	union select 2, 101, 200, 1
	union select 3, 200, 400, 1
	union select 4, 401, 600, 2
	union select 5, 601, 800, 1
    
	union select 6, 100, 200, 3
	union select 7, 200, 400, 4
    union select 8, 75, 150, 3
    union select 9, 50, 350, 3
    ) as test