Order MySQL result

Question

I'm using MySQL/MariaDB. I have an query where I use ORDER BY user_id ASC it gives me the following result:

+---------+
| user_id |
+---------+
| admin1  |
| admin10 |
| admin11 |
| admin2  |
| user1   |
| user10  |
| user11  |
| user12  |
| user13  |
| user2   |
| user20  |
| user21  |
| user22  |
| user23  |
+---------+

I tried the suggested solution ORDER BY LENGTH(user_id), user_id ASC that works great as long as all user_id's starts with user. But if I have user_id's that start with let's say admin then it gives me this:

+---------+
| user_id |
+---------+
| user1   |
| user2   |
| admin1  |
| admin2  |
| user10  |
| user11  |
| user12  |
| user13  |
| user20  |
| user21  |
| user22  |
| user23  |
| admin10 |
| admin11 |
+---------+

But I would like the result to order like this:

+---------+
| user_id |
+---------+
| admin1  |
| admin2  |
| admin10 |
| admin11 |
| user1   |
| user2   |
| user10  |
| user11  |
| user12  |
| user13  |
| user20  |
| user21  |
| user22  |
| user23  |
+---------+

Can I achieve this with SQL?

Take a look at this [question](https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly) — Hamza Abdaoui, Jul 27 '17 at 08:36

score 5 · Answer 1 · answered Jul 27 '17 at 08:35

5

Use the LENGTH function as well as your column

SELECT *
FROM yourtable
ORDER BY LENGTH(user_id), user_id ASC

Output

user_id
user1
user2
user10
user11
user12
user13
user20
user21
user22
user23

SQL Fiddle: http://sqlfiddle.com/#!9/23f4de/3/0

answered Jul 27 '17 at 08:35

Matt

14,906
27
99
149

Sorry I realised that I have `user_id`'s that start with `admin` also. I've updated the question and the SQL Fiddle: http://sqlfiddle.com/#!9/01eef0/1/0 – g3blv Jul 27 '17 at 08:58

score 0 · Answer 2 · answered Jul 27 '17 at 23:16

Are the ids always REGEXP '^[[:alpha:]]+[[:digit:]]+$? If so, then use REGEXP_REPLACE to remove the digits to sort by the alpha part, then remove the letters and add 0 (to turn into a number) to sort the numeric part. Something like:

ORDER BY REGEXP_REPLACE(user_id, '[[:digit:]]+$', ''),
         REGEXP_REPLACE(user_id, '^[[:alpha:]]+', '') + 0

Another idea:

ORDER BY REGEXP_REPLACE(user_id,
              '([[:digit:]]+)',
              RIGHT(CONCAT('0000000', '\\1'), 8))

The principle is to put enough leading zeros on the numeric part so that a string compare will work "correctly".

Note: This uses feature(s) of MariaDB that are not available in MySQL.

Order MySQL result

2 Answers2