[PHP] MySQL Ranges

[beaver6813]

Hi Guys,

I've got a database full of numbers that should be contiguous, but some aren't ie:

23
24
25
26
27
29
30
31
32

I need to get the ranges of this, ie.

24-27 29 30-32

I've had a look about but I can't find any implementations efficient enough for use on large amounts of data, any ideas what MySQL queries could achieve this?

 

[Kate]

It would be tough to achieve the intended result just with a query. You'll need coding. You would need to sort the numbers, then when there is a gap you handle it accordingly.

 

[beaver6813]

It would be tough to achieve the intended result just with a query. You'll need coding. You would need to sort the numbers, then when there is a gap you handle it accordingly.

I managed to use two queries in the end, first one creating a temporary table and then the next using this to select the ranges:
Finding (and grouping) contiguous ranges | Open Query blog

 

[Brujah]

that's just genius, I love to see tricks like this

 

[nasaboy007]

That's a great solution.

Just curious though, would creating a temporary table for a very large query have a performance hit?

 

[beaver6813]

That's a great solution.

Just curious though, would creating a temporary table for a very large query have a performance hit?

Just put that to the test... unfortunately yes, significantly even on only 6000 records it took about 5 seconds to process. So I'm trying to find another pure MySQL solution, any ideas? :D

EDIT: Just for future reference I found the best solution was in the end to retrieve all the numbers from the database and then loop over them all creating ranges. Its a lot faster than the queries, most probably because its all in memory after it retrieves the results from the database.