how to order by '0057','0058', '1', '2', '3', 'A1', 'A2', 'A3' in sql

Question

Can anyone plese give idea to diplay the section data (alpha numeric column) in this below order?

Actual

Expected

I tried this statement a.section REGEXP '^-?[0-9]+$' DESC, length(a.section + 0), a.section this response is like this 1, 2, 0057, 0058, 10, 12, A1, A2. but we expect output like this 0057, 0058,1, 2, 10, 12, A1, A2

What have you tried so far? Where are you stuck? Keep in mind that MySQL 5.6 is horribly outdated and should no longer be used — Nico Haase, Aug 21 '23 at 07:42
Yes, 5.6 is outdated but it's a Symfony 1.4 project that still has not migrated to our client. — Antony, Aug 21 '23 at 07:50
I tried this statement `a.section REGEXP '^-?[0-9]+$' DESC, length(a.section + 0), a.section` this response is like this 1, 2, 0057, 0058, 10, 12, A1, A2. but we expect output like this 0057, 0058,1, 2, 10, 12, A1, A2. — Antony, Aug 21 '23 at 07:56
Please add all clarification to your question by editing it, not to the comment section — Nico Haase, Aug 21 '23 at 08:01
Maybe https://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql helps? — Nico Haase, Aug 21 '23 at 08:03
@Antony I cant understand the pattern of the order. Can you explain more on the sort — Ergest Basha, Aug 21 '23 at 08:58
Assume we have a recode 1, 0058, 10, 12, A1, 2, 0057, A2, 10, B5 in our sections column that needs to be sorted in this order. first order (0057, 0058) second-order (1, 2, 10) finally alphanumeric (A1, A2, B5) so the expected order should be 0057, 0058, 1, 2, 10, A1, A2, B5 — Antony, Aug 21 '23 at 10:07
**Please add all clarification to your question by editing it, not to the comment section** — Nico Haase, Aug 21 '23 at 10:08
MySQL 5.x is reaching end of life two months from now. You should seriously consider upgrading asap. — The Impaler, Aug 21 '23 at 17:24
You can't go against the natural ordering of data type, and expect MySQL to understand what you want and give you the correct result. I am sure even human has difficulty understanding your ordering logic. — Eric, Aug 21 '23 at 17:44
You might be able to achieve the specific sorting for the current data but what will happen when you have data like `00071` etc.? — FanoFN, Aug 22 '23 at 02:23

score 0 · Answer 1 · answered Aug 21 '23 at 17:29

0

You say:

First order: strings that start with '0'.
Second-order: integer values.
Finally: alphanumeric (A1, A2, B5).

If that's the case, you can do:

select col
from (
  select t.col,
    case when col like '0%' then 1
         when col regexp '^[0-9]+$' then 2
         else 3 end as grp
  from t
) x
order by grp, col

answered Aug 21 '23 at 17:29

The Impaler

45,731
9
39
76

Yes, this is what I expected, both answers are working great!!! – Antony Aug 29 '23 at 08:06

FanoFN · Accepted Answer · 2023-08-22T09:45:55.233

Here's a suggestion:

SELECT *
  FROM mytest
ORDER BY CAST(CASE WHEN vals LIKE '0%' THEN 1
        WHEN vals REGEXP '[a-zA-Z]' THEN 999
        ELSE vals    
   END AS SIGNED), vals;

I've tested this query on both of your sample data and it does return the expected results but there's no guarantee that the query will return the expected results if there are more data variant than what you've provided here.

Fiddle : https://dbfiddle.uk/F7FdDD9k

how to order by '0057','0058', '1', '2', '3', 'A1', 'A2', 'A3' in sql

2 Answers2