0

I have below column in my database:

abc
1
2
null
100
6

I want to sort and show these values as per below sequence:

1
2
6
100
abc
null

Anyone please help me to solve it. Consider below query:

select * from test_table where status=1 order by test_column DESC

OR

 select * from test_table where status=1 order by if((test_column = '' OR test_column IS NULL),'999999',test_column) DESC
4
  • What is the datatype of your column? Please give us the output of SHOW CREATE TABLE my_table\G.
    – Vérace
    May 23, 2018 at 7:09
  • Column datatype is MEDIUMTEXT
    – Laty
    May 23, 2018 at 7:16
  • And SHOW CREATE TABLE\G?
    – Vérace
    May 23, 2018 at 7:39
  • select * from table_Name order by Ename; Ename:-what you want arrange alphabetical order provide that specific column name.
    – user184149
    Jun 26, 2019 at 18:56

4 Answers 4

5
select
*
from
t
order by
case 
when col regexp '^[0-9]' then 1
when col regexp '^[a-zA-Z]' then 2
when col = '' or col is null then 3
end
, col * 1 /*this converts to number, so that 100 is not sorted before 2*/
, col /*finally sort strings correctly*/
1
  • 2
    String values can contain digits or another non-letter symbols ('1a', 'b2', 'a b', '1+2', etc.). I think move then 2 to else part is more safe. And regexp for numbers is to be regexp '^[0-9]+$' to avoid '123abc' consider as number.
    – Akina
    May 23, 2018 at 9:34
1

With schema and data like this:

create table t1( data text);

insert into t1 (data) values ('100');
insert into t1 (data) values ('6');
insert into t1 (data) values ('1');
insert into t1 (data) values ('2');
insert into t1 (data) values ('abc');
insert into t1 (data) values (null);

you can run this query:

select data, 
case when length(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(data,'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1',''),'0','')) = 0 then 0 else case when data is null then 99 else 50 end end sort_,
case when length(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(data,'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1',''),'0','')) = 0 then CAST(data AS SIGNED) else 0 end int_
from t1 order by 2 asc, 3 asc;

with result:

+------+-------+------+
| data | sort_ | int_ |
+------+-------+------+
| 1    |     0 |    1 |
| 2    |     0 |    2 |
| 6    |     0 |    6 |
| 100  |     0 |  100 |
| abc  |    50 |    0 |
| NULL |    99 |    0 |
+------+-------+------+
2
  • 1
    And to avoid displaying sort_ and int_, you can SELECT data FROM (bigfoot's query) ORDER BY sort_, int_;- see fiddle here. I'm giving a +1 to this answer!
    – Vérace
    May 23, 2018 at 8:08
  • 1
    This answer is way too complicated.
    – tombom
    May 23, 2018 at 8:44
0

I have found the solution as below : Datatype of column is MEDIUMTEXT so i have converted string to numbers and used below solution :

Order by (test_column+0 != 'zzzzzz' IS NOT TRUE) ,test_column+0 ASC

OR

Order by (test_column+0 != 'zzzzzz' IS NOT TRUE) ,cast(test_column as unsigned) ASC
7
  • 2
    Please accept my deepest condolences. How did you end up with such a mess? May 23, 2018 at 7:25
  • What mess you find with it?
    – Laty
    May 23, 2018 at 7:26
  • MySQL is a relational database. It is unusual to put numbers in a mediumtext column. May 23, 2018 at 7:31
  • Yes agree but it is already designed by someone else so I am working with it this way.Thanks for your opinion.
    – Laty
    May 23, 2018 at 7:36
  • 2
    "designed" is also a very unusual choice of words, in this case. You will learn a great deal from this colleague. Things you should avoid in a next career, I mean. May 23, 2018 at 7:49
0
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.01 sec)

mysql> create table test (txt varchar(8));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into test values ('abc'),(1),(2),(null),(100),(6),(0),('def'),('cde'),(0.4),(0.6);
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from test order by cast(concat('0', txt)+0 as char)=txt DESC, 0+txt, txt;
+------+
| txt  |
+------+
| 0    |
| 0.4  |
| 0.6  |
| 1    |
| 2    |
| 6    |
| 100  |
| abc  |
| cde  |
| def  |
| NULL |
+------+
11 rows in set, 3 warnings (0.00 sec)

Fails on negative numbers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.