Sorting string columns in SQL

Question

I already have the argument for the st column..

mysql> SELECT * FROM t ORDER BY LEFT(st,LOCATE(' ',st)), CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);

+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  3 | a 6  |
|  4 | a 11 |
|  2 | a 11 |
|  5 | b 1  |
|  7 | b 6  |
|  8 | b 12 |
|  6 | b 12 |
+----+------+

this is what it should happen..

+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  3 | a 6  |
|  2 | a 11 |
|  4 | a 11 |
|  5 | b 1  |
|  7 | b 6  |
|  6 | b 12 |
|  8 | b 12 |
+----+------+

can I do this or not via SQL?

D Stanley · Accepted Answer · 2014-07-09 02:51:32Z

1

Well from what you show the only difference is that "ties" are broken by the ID column. If that's the case you can just use that as the third sort field:

SELECT * 
FROM t 
ORDER BY LEFT(st,LOCATE(' ',st)),  
         CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED),
         ID

answered Jul 9, 2014 at 2:51

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jody Over a year ago

This should get you the result you're looking for, but you probably want to split that column up into two others. One for the letter and another for the number. Then you can index the columns and have much faster performance.

AldwinB Over a year ago

@Jody i forgot to ask about that.. rather than splitting them the st data should be without the space. Ex: A1, A6, A11, A11 can sql still process that?

Jody Over a year ago

It would be a string so sorting would not work as expected when you have, for example, A1, A2, A11. The comparison would be lexicographic instead of numeric, so the result would be A1, A11, A2.

Collectives™ on Stack Overflow

Sorting string columns in SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related