sorting a database table with primary keys

[pengwn]

lets say you have

create table BUGS(
bug_id number(20) is primary key,
owner varchar2(50),
subject varchar2(50),
status char(3),
assigned varchar2(8) );

You can use the following to get your table
to a hash of Array and do a sort on which ever column
you like, rather than using ORDER BY.

 my $sth =
  do->("SELECT bug_id, owner, subject, status, assigned FROM BUGS");



while ( 
   my ( $bug_id, $owner, $subject, $status, $assigned ) =
                                $sth->fetechrow ) {
   push @{$bugTable{$bug_id}},
             ( $owner, $subject, $status, $assigned );
}
        

print "Which column would you like to sort by?\n";
print "\t Bug_ID(1), Owner(2), Subject(3) ".
      ", Status(4) or Assigned_To(5) ?: ";

        my $input = <STDIN>;

   SWITCH: for ($input) {
      /1/ && do {
               @srtArr = sort { $a <=> $b } keys %bugTable;
               last;
            };
      /2/ && do {
               @srtArr =
  sort { ${$bugTable{$a}}[0] cmp ${$bugTable{$b}}[0] } keys %bugTable;
               last;
         };
      /3/ && do {
               @srtArr =
  sort { ${$bugTable{$a}}[1] cmp ${$bugTable{$b}}[1] } keys %bugTable;
               last;
         };
      /4/ && do {
               @srtArr =
  sort { ${$bugTable{$a}}[2] cmp ${$bugTable{$b}}[2] } keys %bugTable;
               last;
         };
      /5/ && do {
               @srtArr =
  sort { ${$bugTable{$a}}[3] cmp ${$bugTable{$b}}[3] } keys %bugTable;
               last;
         };
   } # End of SWITCH.

        for my $bug_id ( @srtArr ) {
                print " @{$bugTable{$bug_id}} \n";
        }

[davidrw]

First, why not use the power/native utility of the database to sort w/an ORDER BY clause? (see second code snippet for setting $field)

my $sql = "SELECT bug_id, owner, subject, status, assigned FROM BUGS O
+RDER BY $field";

Second, i personally would prefer selectall_hashref .. simplifies the database retrieval, and makes the sorting clearer and trivial .. (also just a hash works instead of a "switch")

my $bugTable = $dbh->selectall_hashref("SELECT bug_id, owner, subject,
+ status, assigned FROM BUGS");
my %numSorts = ( 1=>'bug_id' );
my %strSorts = ( 2=>'owner', 3=>'subject', 4=>'status', 5=>'assigned' 
+);
my $input = <STDIN>;
chomp $input;
my @srtArr = keys %$bugTable;
if( exists $strSorts{$input} ){
  my $k = $strSorts{$input};
  @srtArr = sort { $bugTable->{$a}->{$k} cmp $bugTable->{$b}->{$k} } @
+srtArr;
}elsif( exists $numSorts{$input} ){
  my $k = $numSorts{$input};
  @srtArr = sort { $bugTable->{$a}->{$k} <=> $bugTable->{$b}->{$k} } @
+srtArr;
}
print " @{$bugTable->{$_}} \n" for @strArr;

[pengwn]

thanks davidrw.
here $choices{$input} mean 1, 2, 3, 4 or 5 right?

[davidrw]

yes .. sorry about that -- I originally had my %choices = (1 => bug_id, 2=>'owner', 3=>'subject', 4=>'status', 5=>'assigned' ); but broke it into %numSorts and %strSorts so that cmp/<=> could be used accordingly, and just didn't copy/paste all the way through...
The code in my above reply has been updated ..

[Anonymous Monk]

Because of his itch hands, that's all. It is a good practice for certain people, so don't stop them from doing that. There always some people hands itch, not a bad thing, not at all.

On the other hand, let db do the sort isnot always the solution. For example order by doe snot play with things like limit in some db implementation. In those cases, you are forced to do the order by yourself. Of course nested query is also a solution. Voila.

[merlyn]

I may be missing something, but why not just ask the database to sort it in the first place? That would have been a much more useful snippet.

[jeremyh]

Sometimes it's useful to re-use the data from a DB query (in a different order) without re-executing the query, so it would be handy to sort it locally.

That's not demonstrated in this example, but I've done this before.

[pengwn]

Thanks "Anonymous Monk" for the backing me :-). Acutally I had used this in one of my web apps since going to the database was a costly affair every time a user sorted on different columns of our bugDB. Even mod_perl was proved to be slow.