Applying ASC sort with RAND?

JasonB has asked for the wisdom of the Perl Monks concerning the following question:

This code extracts data from a MySQL database and uses RAND for the ordering. A second sort by ASC is needed so the output is in a-z order. Any ideas?

sub { 
my $sth;
my $cat_db = $DB->table('Category'); 
$cat_db->select_options ("ORDER BY RAND()", "LIMIT $_[0]");
my $sth = $cat_db->select ( { FatherID => 4}, ['Full_Name','Name'] ); 
my @output; 
while (my $cat = $sth->fetchrow_hashref) {
if (length $cat->{Name} > 23) {
$cat->{Name} = substr($cat->{Name}, 0, 23) . '..';
}
$cat->{URL} = "$CFG->{build_root_url}/" . $cat_db->as_url($cat->{Full_
+Name}) . "/";
push @output, $cat; 
}
return {catrand => \@output};   
}
[download]

Comment on Applying ASC sort with RAND? Download Code

Replies are listed 'Best First'.
Re: Applying ASC sort with RAND? by ikegami (Patriarch) on Apr 17, 2007 at 20:38 UTC
You want the following things to occur in the order in which they are listed: Shuffle Limit Sort I don't think you can do things in that order with a simple SELECT query, although you could probably do the first two use a subquery. (I'm guessing, my SQL is rusty.) Alternatively, Perl can be your "second layer". Replacing `return {catrand => \@output};` with `return {catrand => [ sort { $a->{Cat} cmp $b->{Cat} } @output ]};` would do the trick.	[reply] [d/l] [select]
Re: Applying ASC sort with RAND? by shmem (Chancellor) on Apr 17, 2007 at 18:09 UTC
To what purpose? --shmem _($_=" "x(1<<5)."?\n".q·/)Oo. G°\ / /\_¯/(q / ---------------------------- \__(m.====·.(_("always off the crowd"))."· ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}	[reply]
Re^2: Applying ASC sort with RAND? by JasonB (Initiate) on Apr 17, 2007 at 19:14 UTC
Its part of a content management system. The output is sent to a template at build time. The actual code outputs a selection of categories from the DB, picked at random. This allows me to feature, say 100 category items on the home page. A much better presentation would be if these are then sorted by ascending order.	[reply]
Re: Applying ASC sort with RAND? by Moron (Curate) on Apr 17, 2007 at 20:29 UTC
^C If problem X is how to reference the array so as to give it to the perl sort function ... `my $coderef = # your code above print map { "$_\n" } sort @{$coderef() -> { catrand }};` [download] ^B Of course, just replacing the RAND() with ASC would have exactly the same effect, which is why I think you really want to know about referencing in Perl. __________________________________________________________________________________ ^M Free your mind! Key to hats: ^I=white ^B=black ^P=yellow ^E=red ^C=green ^M=blue - see Moron's scratchpad for fuller explanation.	[reply] [d/l]