CODEWITHSUNDEEP
sqlsorting
AldwinB
AldwinB

Reputation: 31

Sorting string columns in SQL

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?

Upvotes: 0

Views: 52

Answers (1)

D Stanley
D Stanley

Reputation: 152644

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

Upvotes: 1

Related Questions