CODEWITHSUNDEEP
mysqlsqlstringsql-order-by
Muhammad Faryad
Muhammad Faryad

Reputation: 13

Why mysql order by alphanumeric not working

I am facing a problem with the MySQL query. The problem is I have a table called 'members' and a column 'area'. In 'area' column there are alphanumeric values like

1
2
Street # 2
5
78
Street # 1A
Street # 1
Street # 1C
Street # 1B
3

Now What I want? I want to sort like this

1
2
3
5
78
Street # 1
Street # 1A
Street # 1B
Street # 1C
Street # 2

I tried almost every code but not fulfilling my requirements. Last code that is good but not as per my requirement. Currently, I have this code:

SELECT DISTINCT(area) FROM members ORDER BY LENGTH(area), area ASC

One thing that I want to clear that area filed has duplicate values in it.

I'll be thankful if someone helps me.

Thanks in advance

Upvotes: 1

Views: 115

Answers (3)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

One more query:

SELECT DISTINCT(area)
FROM members 
ORDER BY 
    area+0=0 ASC, -- get numbers first
    area+0 ASC, -- order numbers
    area ASC; -- order strings

Live fiddle here SQLize.online

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Extract the first part of the string and order it if it is a number:

select t.*
from t
order by (area regexp '^[0-9]') desc,        -- numbers first
         substring_index(area, ' ', 1) + 0,  -- by number
         area asc                            -- rest alphabetically

Note that this handles the awkward case where the initial number start with 0.

And depending on how you want the strings ordered, you might still want to end with len(area), area as the last two order by keys.

Here is a db<>fiddle.

Upvotes: 1

GMB
GMB

Reputation: 222462

This should work for your sample data:

SELECT DISTINCT area 
FROM members 
ORDER BY (area + 0 > 0 or area = '0') desc, area + 0, area

Demo on DB Fiddle:

| area        |
| :---------- |
| 1           |
| 2           |
| 3           |
| 5           |
| 78          |
| Street # 1  |
| Street # 1A |
| Street # 1B |
| Street # 1C |
| Street # 2  |

Upvotes: 0

Related Questions