Re: ORDER BY Problem

Jim H wrote:

>
> I have PHP code containing:
> [Quoted] [Quoted]
> $query = "SELECT name, level, date_entered
> FROM $table
> WHERE name = 'Annual'
> ORDER BY level DESC
> LIMIT 10";
>
> Field "level" is VARCHAR(13) and can contain either all numeric
> (numeric string) or all alpha data, but for records where name =
> "Annual" the content is always all numeric (numeric string)
>
> So, after stepping thru $query and printing the content of "level" I
> get the following desired result:
>
> 580
> 393
> 346
> 192
> 157
> 156
>
> And after adding another record containing level = 1174 I get the
> following undesired result:
>
> 580
> 393
> 346
> 192
> 157
> 156
> 1174
>
> What I want is:
>
> 1174
> 580
> 393
> 346
> 192
> 157
> 156
>
> What am I doing wrong?

You are not ordering by numeric order, but by string order. You need to convert <<level>> into a number.

Try changing the ORDER BY to sort on a numeric field. Something like...

  $query = "SELECT name, level, date_entered

             FROM $table
             WHERE name = 'Annual'
             ORDER BY 0+level DESC
             LIMIT 10";

Note: this only works when <<level>> contains numerics only. Should <<level>> contain alphabetics, this won't behave as you requested.

-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request