First:

  SELECT
    group, somedata
  FROM
    table
  ORDER BY
    group, somedata
[download]

Next:

  my $lastgroup = undef;
  while ( my $row = $sth->fetchrow_arrayref() ) {
    if ( $lastgroup ne $row->[0] ) {
      print( $row->[0] . "\n" );
      $lastgroup = $row->[0];
    }
    print( "  " . $row->[1] . "\n" );
  }
[download]

  my $lastgroup = undef;
  while ( my $row = $sth->fetchrow_arrayref() ) {
    if ( ( !defined( $lastgroup ) ) || 
         ( $lastgroup ne $row->[0] ) )
    {
      print( $row->[0] . "\n" );
      $lastgroup = $row->[0];
    }
    print( "  " . $row->[1] . "\n" );
  }
[download]

Hey,

To select the individual groups only once, you can also use the DISTINCT clause for the select statement.

Then you can use the results in a prepared cache to find the results for each individual group. monarch seems to have a correct solution also. But I'd thought I post something about the DISTINCT clause because it solves your first problem really well.

my $dbh = DBI->connect('DBI:mysql:DB', 'user_n', $pass) or die "Failed
+ connecting to DB " . DBI->errstr;

    my ($groups, $results);
    my $sth = $dbh->prepare_cached(<<SQL);
SELECT DISTINCT group from table
SQL

    $sth->execute or die "executing: ", $dbh->errstr;;
    $sth->bind_columns(undef, \$groups);
    #bind the results--only contains unique group results.

    my $sth_x = $dbh->prepare_cached(<<SQL);
SELECT somedata FROM table where group = ?
SQL
            my $found = 0; my $found_x = 0;
                print "<b>$groups</b><hr>";

                while ($sth->fetch) {
                 $found++;

    $sth_x->execute($groups) or die "executing: ", $dbh->errstr;  
    #find results using the results from the first query
    $sth_x->bind_columns(undef, \$results);


        
                    while($sth_x->fetch) {
                    $found_x++;
                    print "$results<br>";
                    }
                    unless($found_x) { print "empty for this group"; }

        print "<br>";
        my $found_x = 0; 
        #reset 2nd search found for next group result query.
                }

        unless ($found) { print "no groups matched"; }
[download]

perleager

Somewhat off topic, but it sounds like you need to look at the group by clause.

Actually, group by does very little in this case. Sort would work just fine for this situation (see monarch's answer ... that's the type of logic that I would use.)

In these situations, I think the logic outside of the SQL is more important for the final presentation than something you can do through SQL.

So, if 'group by' isn't useful here, what is it really good for? Well, it's good for when you're trying to aggregate records, such as the following.

SELECT groupfield, SUM(somedata)
FROM tablename
GROUP BY groupfield
[download]

or

SELECT groupfield, MAX(somedata)
FROM tablename
GROUP BY groupfield
[download]

In these situations, you only get one row returned per 'group'. There's also the rarely used (in my experience) HAVING clause.

GROUP BY comes in very handy here, actually. (with MySQL's handy GROUP_CONCAT() function, versions 4.1 and up):

SELECT Group, GROUP_CONCAT(SomeData ORDER BY SomeData SEPARATOR ',')
FROM table
GROUP BY `Group`
[download]

In the OP's example, this would result in the following data that would be trivial to loop through and split on the separator chosen in your SQL:

green       3,9
orange      8
purple      4
red         1,5,6,7
yellow      2
[download]

-- Brian

my $sth = $dbh->prepare( "select group, somedata from sometable" );

$sth->execute;

my $rowrefs = $sth->fetchall_arrayref;
my %grouphash;

for my $row ( @$rowrefs ) {
    push @{$grouphash{$$row[0]}}, $$row[1];
}
for my $group ( sort keys %grouphash ) {
    print "$group\n";
    print "  $_\n" for ( sort @{$grouphash{$group}} );
}
[download]

updated to fix brackets on $$row[1] (I didn't mean to use curlies there).