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
  • What is the datatype of your column? Please give us the output of SHOW CREATE TABLE my_table\G. – Vérace May 23 '18 at 7:09
  • Column datatype is MEDIUMTEXT – Keith May 23 '18 at 7:16
  • And SHOW CREATE TABLE\G? – Vérace May 23 '18 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 at 18:56
4
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
    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 '18 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 |
+------+-------+------+
  • 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 '18 at 8:08
  • This answer is way too complicated. – tombom May 23 '18 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
  • 2
    Please accept my deepest condolences. How did you end up with such a mess? – Gerard H. Pille May 23 '18 at 7:25
  • What mess you find with it? – Keith May 23 '18 at 7:26
  • MySQL is a relational database. It is unusual to put numbers in a mediumtext column. – Gerard H. Pille May 23 '18 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. – Keith May 23 '18 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. – Gerard H. Pille May 23 '18 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, privacy policy and cookie policy

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