CODEWITHSUNDEEP
mysql
Ank
Ank

Reputation: 6260

Sorting in mysql

I have a column abc in table t1. The column has values 

A1
A2   
A3
A4
.
.
A12
B1
B2
.
.
.
B12
C1
C2
.
.
.
C12
H1
.
.
H12

I wanna sort them such that the output is 

A1
B1
C1
.
.
H1
A2
B2
C2
.
.
.
H2
.
.
.
.
A12
.
.
.
H12

A select * from abc statement gives A1,A10,A2.... as output. I am trying to use SUBSTR but haven't gotten it right.

Upvotes: 1

Views: 208

Answers (5)

Pargunan
Pargunan

Reputation: 65

SELECT  Square
FROM    Table1
ORDER   BY         
        CASE WHEN Square REGEXP '^[A-Z]{2}'
            THEN 1 
            ELSE 0
        END ASC,
        CASE WHEN Square REGEXP '^[A-Z]{2}'
            THEN LEFT(Square, 2)
            ELSE LEFT(Square, 1)
        END ASC,
        CASE WHEN Square REGEXP '^[A-Z]{2}'
            THEN CAST(RIGHT(Square, LENGTH(Square) - 2) AS SIGNED)
            ELSE CAST(RIGHT(Square, LENGTH(Square) - 1) AS SIGNED)
        END ASC

Upvotes: 0

Crsr
Crsr

Reputation: 624

order by lpad(date_created,1,0) ASC

@bfavaretto i think if you use -1 in length colname, and you have 3 characters like in example you will have some strange results.

Upvotes: 0

Randy
Randy

Reputation: 16677

maybe this:

ORDER BY LEFT(colname,1), RIGHT(colname, LENGTH(colname)-1)

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

This should do it;

SELECT * FROM TEST
ORDER BY SUBSTRING(VALUE, 2) + 0, 
         SUBSTRING(VALUE, 1, 1);

Demo here.

Upvotes: 5

bfavaretto
bfavaretto

Reputation: 71908

Considering the format is always a single alpha char followed by any number of digits:

ORDER BY RIGHT(colname, LENGTH(colname)-1)

Upvotes: 0

Related Questions