Missing Nubers

"Brian" wrote in message


> I am trying to work out if there are missing numbers in a range stored in
> a database table.

SELECT a.Id + 1 Missing
FROM foo a
LEFT JOIN foo b
ON b.Id = a.Id + 1
WHERE b.Id IS NULL

(that's one way...and faster than working over a resultset in php to get the
same result)

> The first problem is the number range is prefixed with UR (eg UR12345),
> what I need to
>
> try and work out it are there any numbers in the range missing, the range
> should be from
>
> UR1000 to UR30000, what the best way to try and work this out?

cast the result of right(a, length(a) - 2) as an integer and plug that into
the query above...changing the names to your appropriate tables and fields.
 >> Stay informed about: Missing Nubers 

"Jerry Stuckle" wrote in message

> Brian wrote:
>> Hi all
>>
>>
>>
>> Sorry for the cross postings but there may be a simple solution that can
>> be done in SQL.
>>
>> I am trying to work out if there are missing numbers in a range stored in
>> a database table.
>>
>>
>>
>> The first problem is the number range is prefixed with UR (eg UR12345),
>> what I need to
>>
>> try and work out it are there any numbers in the range missing, the
>> range should be from
>>
>> UR1000 to UR30000, what the best way to try and work this out?
>>
>>
>>
>> Thanks
>>
>>
>>
>> Brian
>>
>>
>
> This is a bit off target for alt.php. But you could also try a newsgroup
> for your database, i.e. comp.databases.(your_rdbms_here).

it looks like he's well aware of the ot-ishness of the post. can't you take
the whole 30 seconds it takes to write the damn thing! this ain't rocket
science you know.
 >> Stay informed about: Missing Nubers 

Brian wrote:
> Hi all
>
>
>
> Sorry for the cross postings but there may be a simple solution that can be
> done in SQL.
>
> I am trying to work out if there are missing numbers in a range stored in a
> database table.
>
>
>
> The first problem is the number range is prefixed with UR (eg UR12345), what
> I need to
>
> try and work out it are there any numbers in the range missing, the range
> should be from
>
> UR1000 to UR30000, what the best way to try and work this out?

As far as I know, you would need to do

for($i=1000;$i<=30000;$i++) {
$query="SELECT count(*) FROM table WHERE column='UR{$i}'";
$res=mysqli->query($query);
if(!$res->num_rows) {
echo "Missing number is UR{$i}\n";
}
}


--

//Aho
 >> Stay informed about: Missing Nubers 

"J.O. Aho" wrote in message


> for($i=1000;$i<=30000;$i++) {
> $query="SELECT count(*) FROM table WHERE column='UR{$i}'";
> $res=mysqli->query($query);
> if(!$res->num_rows) {
> echo "Missing number is UR{$i}\n";
> }
> }

aho, i'm not going to say that's a crazy way to do it...but, just look at
the resources you're wasting! you really want to run a query for *every*
iteration? even this is more attractive...but butt-ugly:

$sql = array();
for ($i = 1000; $i <= 3000; $i++)
{
$sql[] = "
SELECT 'UR" . $i . " Id ,
COUNT(*) Missing
FROM table
WHERE column = 'UR" . $i . "
";
}
$sql = "
SELECT *
FROM
(
" . implode(' UNION', $sql) . "
)
WHERE Missing = 0
";
$query = mysqli->query($sql);
echo '<pre>' . print_r($query, true) . '</pre>';
 >> Stay informed about: Missing Nubers