0

I have records with column record_id which is constructed from letters and numbers

eg 

a1
a2
a11
a12
b1

If i sort this I would get

a1
a11
a12
a2
b1

i was wondering if there is a way to sort this by letter then by the value of the number as the following

a1
a2
a11
a12
b1
Henok Teklu
  • 528
  • 3
  • 9

1 Answers1

1
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(string CHAR(3) NOT NULL);

INSERT INTO my_table VALUES
('a2'),('a11'),('a12'),('b1');

select * FROM my_table ORDER BY string;
+--------+
| string |
+--------+
| a11    |
| a12    |
| a2     |
| b1     |
+--------+

select * FROM my_table ORDER BY string+0;
+--------+
| string |
+--------+
| a2     |
| a11    |
| a12    |
| b1     |
+--------+

Now seriously consider normalising your data.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • Thanks this works like charm, about you normalizing remark the table has many other columns I just took the column I wanted sorted – Henok Teklu May 19 '20 at 13:20