1

I have table with data like below, I tried order by many options but always somethink is wrong. My sql result:

select type from types
order by type ASN;

TYPE 1
TYPE 10
TYPE 11
TYPE 12
TYPE 13
TYPE 14
TYPE 15
TYPE 16
TYPE 17
TYPE 18
TYPE 19
TYPE 2
TYPE 20
TYPE 21

How you see "TYPE 2" is under type 19, I would like get result like below

TYPE 1
TYPE 2
TYPE 10
TYPE 11
TYPE 12
TYPE 13
TYPE 14
TYPE 15
TYPE 16
TYPE 17
TYPE 18
TYPE 19
TYPE 20
TYPE 21

My data in table have diffrent type not always 4 letters sometimes have 3 letters but always is one space between letters and number

marek
  • 11
  • 1
  • you right, probably best solution will be separate type and numbers in two diffrent columns, and order by column number and after that select data connected in php, mysql solution not exist for my issue? – marek Jul 27 '18 at 17:54
  • 2
    Possible duplicate of [MySQL 'Order By' - sorting alphanumeric correctly](https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly) – Progman Jul 27 '18 at 17:59
  • There are lot of good suggestions for this problem in the answer linked by @Progman. – RToyo Jul 27 '18 at 18:05
  • @marek 01, 02, ... instead of 1, 2, ... – Eric Jul 27 '18 at 18:06
  • [Here](https://stackoverflow.com/a/48602292/5563083) I wrote a function to use for a *natural sort*. But it requires MariaDB 10 or MySQL 8. – Paul Spiegel Jul 27 '18 at 18:09

3 Answers3

2

If you only have data that is one word followed by a number, you can use substring_index to split your string at the space. Then you can order by those two different values. Note that you need to convert the number to an int so that MySQL sorts it numerically, rather than alphanumerically. 

select 
  *
from types
order by
  substring_index(type, " ", 1),
  convert(substring_index(type, " ", -1), signed int)

Edit: I should point out that the reason I didn't suggest simply doing the typical order by length work around, is because the OP said the length of the type column values would be variable (3 or 4 chars).

This is the method that would normally be suggested if you had the exact same prefix before your numbers (eg if it was always "type ###"):

order by char_length(type), type
RToyo
  • 2,877
  • 1
  • 15
  • 22
0

The underlying column is a string and you're doing a string sort, you need to cast to a number.

CONVERT(type, UNSIGNED INTEGER)

Would convert it to a number.

Eric Yang
  • 2,678
  • 1
  • 12
  • 18
  • Keep in mind that because the `type` column contains a string as well, a straight convert of the column will return `0` every time. – RToyo Jul 27 '18 at 18:03
  • To clarify my previous comment: Converting a value that starts with an int, such as `10 Type` would return the int value, which could be used for sorting. But because this data is `Type 10`, this method won't work. I assume your answer is suggesting something like `select * from types order by convert(type, unsigned integer), type`? – RToyo Jul 27 '18 at 18:11
0

According to sample data below will be work by using substring and POSITION

 select * from types 
 order by CONVERT( substring(type, POSITION(' ' IN type)),UNSIGNED INTEGER) asc

another way by using SUBSTRING_INDEX

select * from types 
order by CONVERT(SUBSTRING_INDEX(type,' ',-1),UNSIGNED INTEGER) asc

http://www.sqlfiddle.com/#!9/314d43/1

http://www.sqlfiddle.com/#!9/314d43/3

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • Just playing devil's advocate here: The OP's question stated "My data in table have diffrent type not always 4 letters sometimes have 3". In the case of this answer, you are only sorting by the number, and not by the text at the beginning. If the table has a row with a value of `ABC 123`, it would get sorted to the bottom of the table, even though "ABC" comes before "TYPE". – RToyo Jul 27 '18 at 18:14