Sorting the contents of an array, mysql

[FreeBASE]

Basically I want to sort data returned by an sql query.

my $sth = $dbh->prepare("SELECT Title, Age, name FROM Jobs ORDER BY Title"

while (my ($title, $age, $name) = $sth->fetchrow_array) {

print &quot;$title<br>&quot;;

}

When using &quot;ORDER BY Title&quot; the data returned looks like:

Electrician 1
Thief 10
Brick Layer 11
Chef 2
Homemaker 3
Officer 4
Desk Clerk 5
Pornstar 6
Dj 7
Pimp 8
Nurse 9

But somehopw I need to sort this data to look like:

Electrician 1
Chef 2
Homemaker 3
Officer 4
Desk Clerk 5
Pornstar 6
Dj 7
Pimp 8
Nurse 9
Thief 10
Brick Layer 11

Any suggestions.. Thanks in advance

 

[icrf]

This is just a guess, but if the numbers are in a numeric field, they should be sorted numerically. It appears to be doing an ascii sort, meaning it's a character field in the database. If you must use the character field, I think you have to pad each entry with enough leading zeros that an ascii sort will work (08 lt 11).

You could take everything out of the database, then sort it numerically in perl, but I'd suggest against that. I don't know if there's an SQL method (or if it's DB dependent) to force/cast a field's type. ----------------------------------------------------------------------------------
...but I'm just a C man trying to see the light

 

[pho01]

store everything in an array and sort it.

push (@records, $title);
#add title or the whole record to the array
sort keys %records;
foreach $record(@records)
{
print $record,&quot;\n&quot;;
}

Hope that helps!

 

[icrf]

The sort function doesn't alter its argument at all, it returns it and you have to use it or save it. For a one-time sort like that, the sort is often the array in the foreach loop. Otherwise, you can save the return to an array, to loop over it many times in the future, jsut using each array element as a hash key.

foreach (sort keys %records) {}
#or
@recordKeys = sort keys %records;
foreach (@recordKeys) {}

In the end, you can use that if you can't alter the database to be numeric or add padding, it just eats memory if the return list is large. ----------------------------------------------------------------------------------
...but I'm just a C man trying to see the light