Re: ORDER BY Problem

From: J.O. Aho <user_at_example.net>
Date: Sat, 16 Dec 2017 18:11:02 +0100
Message-ID: <f9l2d6F2p54U1_at_mid.individual.net>


On 12/16/17 16:58, 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
> 
> 
> 
> [root_at_test]> select a,b from testSort;
> +------+------+
> | a    | b    |
> +------+------+
> |    1 | z    |
> |    2 | y    |
> |    3 | x    |
> +------+------+
> 3 rows in set (0.00 sec)

a is supposed to be varchar and not int.

> 
> [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)

It will only work if you are selecting only the rows where you have numeric values, if you mix it with the alpha only values, you are back on square one and you have also warnings as the numeric type don't match the alphanumeric type.

select * from a order by case when 0+level>0 then 0+level else level end; +-------+----------+
| level | name |
+-------+----------+

| 1     | one      |
| 11    | one one  |
| 2     | two      |
| a     | alpha a  |
| b     | alpha b  |

+-------+----------+
5 rows in set, 2 warnings (0.00 sec)

This is my table:
CREATE TABLE `a` (`level` varchar(10) DEFAULT NULL, `name` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from a;
+-------+---------+
| level | name |
+-------+---------+

| 1     | one     |
| 2     | two     |
| a     | alpha a |
| b     | alpha b |
| 11    | one one |

+-------+---------+

Using your order by generates the following result (I'm removing all none numeric levels):

select * from a where name not like 'alpha%' order by case when 0+level>0 then 0+level else level end;
+-------+---------+
| level | name |
+-------+---------+

| 1     | one     |
| 11    | one one |
| 2     | two     |

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

As you see, sorting order is alphanumeric.

If I do convert the numeric values to int in the select and use an alias, then it works a lot better:

select 0+level id, name from a where name not like 'alpha%' order by id; +------+---------+
| id | name |
+------+---------+

|    1 | one     |
|    2 | two     |
|   11 | one one |

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

of course this works badly when you include those alpha only values as seen below:
select 0+level id, name from a order by id; +------+---------+
| id | name |
+------+---------+

|    0 | alpha a |
|    0 | alpha b |
|    1 | one     |
|    2 | two     |
|   11 | one one |

+------+---------+
5 rows in set, 4 warnings (0.00 sec)

[Quoted] The only thing that will solve this, is a proper database design.

-- 

 //Aho
Received on Sat Dec 16 2017 - 18:11:02 CET

Original text of this message