If I understand correctly, you want the minimum distance (along with the stop_reference) for each service_id. If you didn't need the stop_reference, you could just do a GROUP BY:

-- not what you want
SELECT service_id, min(distance) FROM table
GROUP BY service_id;
[download]

But then you lose the stop_reference information, because you've "grouped" that away.

So what I'd probably do is use ORDER BY:

SELECT stop_reference, service_id, distance FROM table
ORDER BY service_id, distance;
[download]

Then you just print out (or store) every time the service_id changes:

my $last_service_id = -8675309;    # for a good time, call
while ($sth->fetch()) {
    if ($service != $last_service_id) {
        print "min dist for service '$service' = '$distance' at stop '
+$stops'\n";
        $last_service_id = $service;
    }
}
[download]

Ummm... You know you can GROUP BY multiple fields, right?

SELECT stop_reference, service_id, min(distance)
FROM table
GROUP BY stop_reference, service_id ;
[download]

The basic rule is that you can include as many fields in a summary query as you like, provided that each one of them has either a summary operation (such as min()) or a GROUP BY applied to it. (This does rule out anything like SELECT * without going to a subquery, but, then, I only use SELECT * when I'm querying by hand anyhow. Using it in my code carries too much risk of breakage if the table's columns get reordered.)

I thought about the double GROUP BY, but I think that'd only work if stop_reference is an ID-like column, with distinct values, right? I was assuming that since stop_reference is referring to bus stops, that the same stop_reference might occur more than once (for example where two or more bus lines intersect). In that case, I think the GROUP BY would no longer work because you'd "group away" some extra rows, right?

That is exactly the answer I was looking for thanks so much to everyone for their advice and help and especially to ForgotPasswordAgain for his answer :-) You guys really are Perl legends!! Some day ill hope to repay you with some knowledge of my own!

An SQL guru could probably show you how to do this in the query statement (perhaps using a MIN() function), rather than retrieving a lot of data that you really aren't interested in and then filtering through it, but I don't have enough mojo to whip it off without testing and I don't have time to test right now. :-)

That said, you could declare a hash (my %bus_stops) before the while loop and then do something like this in the loop itself:

    next if( $bus_stops{$service}{dist} < $distance );
    $bus_stops{$service}{dist} = $distance;
    $bus_stops{$service}{stop_ref} = $stops;
[download]

You may also find the fetchall_arrayref() and fetchall_hashref() methods useful. They allow you to retrieve all of the results at once (rather than looping over a fetch statement), but the practicality of doing that depends on how many results you plan to retrieve. :-)

SELECT DISTINT service_ID from .....

Fetch into array and loop...
foreach (serviceID)
{
  SELECT stop_reference, distance from XXX
  WHERE service_ID = :p1
    AND distance = (SELECT min(distance) from XXX where service_id = :
+p1)
  
}
[download]

select a.* from table_name a 
  join (
    select min(distance) as min_dist, 
           service_id as min_id
           from table_name
           group by service_id
    ) b 
  on (a.distance = min_dist and a.service_id = min_id);
[download]

/is not a guru
//tried it and it worked for me
[download]

Please show the full query. It may be possible to modify the query, itself, to do what you ask.

If modifying the query to do all the work isn't possible, and presuming service_id is the route number, I would loop through the data, filling an array indexed by service_id. When the distance is less than the existing distance, replace it with the distance just read. Filling in your while loop with some guaranteed-to-look-like-code pseudo-code:

my @stop_list;
my @distance;
while($sth -> fetch()){
    if((not defined($distance[$service]) or
       $distance[$service] > $distance) {

         $stop_list[$service] = $stops;
         $distance[$service] = $distance;
     }
}
foreach $service (@stop_list) {
   print "Stop: $stop_list[$service] distance: $distance[$service]\n"
}
[download]

corrected markup

use strict;
use warnings;

use Data::Dumper;

sub pretendFetch
{
    my $cannedFetch = [
       [6200249230, 2, 247.695377429616],
       [6200200581, 1, 131],
       [6200249220, 2, 394.81641303269],
       [6200249240, 2, 148.32734070292],
       [6200249269, 1, 127.84583439484],
       [6200249250, 2, 394.507287638644],
       [6200248437, 1, 234.38349845484],
       ];
    return $cannedFetch;
}

my %service_id =
   map  { $_->[1] => {
                        stop_reference => $_->[0],
                        distance => $_->[2]
                     }
        }
   sort { $b->[2] <=> $a->[2] }
   @{ pretendFetch() };

print Data::Dumper->Dump([\%service_id], [q{*service_id}]);
[download]

Which produces

%service_id = (
                '1' => {
                         'distance' => '127.84583439484',
                         'stop_reference' => '6200249269'
                       },
                '2' => {
                         'distance' => '148.32734070292',
                         'stop_reference' => '6200249240'
                       }
              );
[download]

I hope this is of interest.

Cheers,

JohnGG