Re: ORDER BY Problem

From: Luuk <luuk_at_invalid.lan>
Date: Sat, 16 Dec 2017 16:58:49 +0100
Message-ID: <5a3542b8$0$10012$e4fe514c_at_news.xs4all.nl>

[Quoted] 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 [root_at_test]> select a,b from testSort; +------+------+
| a | b |
+------+------+

|    1 | z    |
|    2 | y    |
|    3 | x    |

+------+------+
3 rows in set (0.00 sec)

[root_at_test]> select a,b from testSort order by case when 0+a>0 then 0+a else a end desc;
+------+------+
| a | b |
+------+------+

|    3 | x    |
|    2 | y    |
|    1 | z    |

+------+------+
3 rows in set (0.00 sec)

[root_at_test]> select a,b from testSort order by case when 0+b>0 then 0+b else b end desc;
+------+------+
| a | b |
+------+------+

|    1 | z    |
|    2 | y    |
|    3 | x    |

+------+------+
3 rows in set (0.00 sec) Received on Sat Dec 16 2017 - 16:58:49 CET