Re: ORDER BY Problem
From: Luuk <luuk_at_invalid.lan>
Date: Mon, 18 Dec 2017 20:34:21 +0100
Message-ID: <5a38183a$0$10015$e4fe514c_at_news.xs4all.nl>
>>> 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.
>>>
Date: Mon, 18 Dec 2017 20:34:21 +0100
Message-ID: <5a38183a$0$10015$e4fe514c_at_news.xs4all.nl>
On 17-12-17 22:01, Jim H wrote:
> 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 >> > [Quoted] > Haven't tested it with my data, but it looks like a complete solution > to this rank beginner. > > Thank you. >
LOL, you are not saying i'm a beginner, are you ?
;) Received on Mon Dec 18 2017 - 20:34:21 CET