Finding Ranges

[CrashRoX]

I have a table based on numeric id's. Im trying to perform a select and then return the numeric range of the ids. For example if my select returned data that looked like:

+----------+--------------------+
| id | name |
+----------+--------------------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
|12 | F |
|13 | G |
|22 | H |
|23 | I |

The result I am looking for from this is:
1-5
12-13
22-23

Whether those are broken up as low and high or just have the dash is irrelevant. But an help in trying to find these ranges is appreciated.

[bogu]

Try this:

$sql = "SELECT * FROM `table` ";
$sql.= "ORDER BY id";
$que = mysql_query($sql) or die(mysql_error());
if (($nr_rez = mysql_num_rows($que))) {
	$min = 0;
	while ($rez = mysql_fetch_assoc($que)) {
		if (empty($min)) {$min = $rez['id']; $max = $rez['id'];}
		elseif (($max+1)!=$rez['id']) {
			echo "{$min} - {$max}";
			$min = $rez['id']; $max = $rez['id'];
		} else $max = $rez['id'];
	}
}mysql_free_result($que);

Btw, is not tested ...

[CrashRoX]

bogu I see where you are going with that. I appreciate the help. I was actually looking to do all of it in the SQL. Any other suggestions?

[CrashRoX]

Well that only look a total of three hours... If anyone is interested...

SELECT a.id AS
START , min( c.id ) AS
END FROM (
tbs a
LEFT OUTER JOIN tbs b ON a.id = b.id +1
)
LEFT OUTER JOIN (
tbs c
LEFT OUTER JOIN tbs d ON c.id = d.id -1
) ON a.id < c.id
WHERE b.id IS NULL
AND d.id IS NULL
GROUP BY a.id