Please check the above results output. As you can see after DT - 10 it displays DT - 100. But I need to display DT - 12 instead of DT - 100. I tried this in two ways, but the results are same.
SELECT *
FROM fas_details
WHERE equipment_no LIKE 'DT%'
ORDER BY CAST(equipment_no AS DECIMAL(10,2))
SELECT *
FROM fas_details
WHERE equipment_no LIKE 'DT%'
ORDER BY equipment_no * 1 ASC, equipment_no ASC
Already checked following questions,
equipment_no is a string and it is sorted as such.
You must extract the integer part after the dash and (implicitly) convert it to an integer and then sort:
SELECT *
FROM fas_details
WHERE equipment_no LIKE 'DT%'
ORDER BY SUBSTRING_INDEX(equipment_no, '-', -1) + 0
Would equipment_no be always in DT - * format?
Yes it is @AJ. It always in the same format.
The numbers in the equipment_no will be treated as a string and sorted on such basis. You have to extract these numbers. You can use SUBSTRING_INDEX for this purpose and then cast the result as Integer to sort it.
SELECT *
FROM fas_details
WHERE equipment_no LIKE 'DT%'
ORDER BY CAST(SUBSTRING_INDEX(equipment_no, '-', -1) AS UNSIGNED) ASC;
We can use SUBSTRING_INDEX here but we also must cast the output to an integer before sorting:
SELECT *
FROM fas_details
WHERE equipment_no LIKE 'DT%'
ORDER BY CAST(SUBSTRING_INDEX(equipment_no, ' - ', -1) AS UNSIGNED);
When sorting on a piece of the 'equipment_no', you also need to notice the alphanumeric part before the number.
When i.e. having values like 'ET - 0' en 'ET - 9' , than 'ET - 9', you do not want an output like:
| ET - 0 |
| DT - 01 |
| DT - 02 |
| DT - 03 |
| DT - 04 |
| ET - 9 |
| DT - 10 |
This will sort more correct:
select * from equipment
order by
substring_index(equipment_no,'-',1),
cast(trim(substring_index(equipment_no,'-',-1)) as unsigned);
output:
| DT - 100 |
| DT - 101 |
| DT - 102 |
| ET - 0 |
| ET - 9 |
Last remark, sorting like this will be slow when there are 'a lot of' records because every record (in the selection) has to be fetched to get the sort order!
