Home -> Usenet -> c.d.mysql -> Re: ORDER BY Problem

Re: ORDER BY Problem

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Sat, 16 Dec 2017 11:04:19 -0500
Message-ID: <p13g64$q4c$1_at_dont-email.me> 






Luuk wrote:


> On 16-12-17 16:45, Lew Pitcher wrote:
>> Jim H wrote:
>> 




>>>

>>> I have PHP code containing:

>>>

>>>  $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.
>> 
> 
> ORDER BY
> CASE WHEN 0+level>0
> THEN 0+level
> ELSE level
> END





Cool! I love it.



Thanks for teaching me something new  :-)


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


Received on Sat Dec 16 2017 - 17:04:19 CET












Original text of this message