On Tuesday, April 1, 2003, at 12:31 AM, allan juul wrote:
hi
using a postgres db i have run into a sort order problem because i need to
order by a varchar type field/column.
im not aware of any function in postgres that might order the result
set "correctly" beforehand so i guess i want to post-sort the result-set with
perl.
i'm looking for something pretty effecient as the datastructure could be
potentially big
That statement means you should be looking for a sort that can be done in the database, not in perl, because then you can keep an ordering in advance (by using an index on the proper column).
But see below for why you can't do that.
so in the end i need this order:
1 2 3 9 10 11 11a 11b 110
Those 'a' and 'b' suffixes are, of course, fouling up the works. Essentially, you are packing more than one kind of data into a single field - you've got both a "major" team number and a "minor" team letter, sort of like version numbering ideas (10.3.2, 4.2alpha, etc.). I'm not aware of a way to handle that in the database, but I'm no Pg expert.
In perl, it's pretty easy:
my @sorted = sort { $a <=> $b
or
$a cmp $b } @list;But of course, that requires having two copies of the whole array in memory.
-Ken
