Re: ORDER BY Problem

On Sat, 16 Dec 2017 16:58:49 +0100, in
<5a3542b8$0$10012$e4fe514c_at_news.xs4all.nl>, Luuk <luuk_at_invalid.lan> 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
>

Haven't tested it with my data, but it looks like a complete solution to this rank beginner.

Thank you.

-- 
Jim H