CODEWITHSUNDEEP
sqlsql-order-by
Airikr
Airikr

Reputation: 6436

Sort data after letters first, then digits

Example:

32 BB
12 A
84 H
13 H
34 C
16 C
115 H
18 C

I want to order them like this:

12 A
32 BB
16 C
18 C
34 C
13 H
84 H
115 H

Here's what I've tried so far:

SELECT *
FROM people
ORDER BY IF(data_name RLIKE '^[a-z]', 2, 3), data_name

That SQL query sorts them similar to how they are unsorted. How can I accomplish this?

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If the data is in two columns (which is how I interpret the question and sample data), then simply do:

order by col2, col1

If the data is in one column, then I will guess from the rlike that you are using MySQL, you can do:

order by substring_index(col, ' ', -1), substring_index(col, ' ', 1) + 0

Upvotes: 1

Related Questions