sorting a database table with primary keys

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";
        }
[download]

Comment on sorting a database table with primary keys Download Code

Replies are listed 'Best First'.
Re: sorting a database table with primary keys by davidrw (Prior) on Apr 03, 2006 at 13:05 UTC
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";` [download] 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; [download]	[reply] [d/l] [select]
Re^2: sorting a database table with primary keys by pengwn (Acolyte) on Apr 05, 2006 at 09:27 UTC
thanks davidrw. here $choices{$input} mean 1, 2, 3, 4 or 5 right?	[reply]
Re^3: sorting a database table with primary keys by davidrw (Prior) on Apr 05, 2006 at 12:29 UTC
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 ..	[reply] [d/l] [select]
Re^2: sorting a database table with primary keys by Anonymous Monk on Apr 04, 2006 at 05:06 UTC
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.	[reply]
Re: sorting a database table with primary keys by merlyn (Sage) on Apr 03, 2006 at 13:04 UTC
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. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply]
Re^2: sorting a database table with primary keys by jeremyh (Beadle) on Apr 12, 2006 at 00:59 UTC
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.	[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.
Re: sorting a database table with primary keys by pengwn (Acolyte) on Apr 04, 2006 at 08:38 UTC
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.	[reply]