CODEWITHSUNDEEP
mysqlsortingconcatenation
user3749800
user3749800

Reputation: 93

Sort CONCAT values properly MYSQL

After performing simple col1/col2 computations and then using CONCAT to add a literal % sign, I am unable to successfully sort them in order.

Initially my results sorted DESC were like

9%
8%
75%
65%
100%

After an update of:

   CASE WHEN ROUND(col1/col2 *100) = 100 
        THEN CONCAT(100, '%') 
        ELSE LPAD(CONCAT(ROUND(col1/col2 *100),'%'),3,'0') 
        END AS Percent

I was able to successfully sort the single and double digit columns by adding a 0 in front of the single digit, but i was left with the 100 being out of place

75%
65%
09%
08%
100%

Thoughts?

Upvotes: 0

Views: 3874

Answers (2)

Siddharth Kumar
Siddharth Kumar

Reputation: 1715

Seems you have already added the % symbol to the values, so you can retrieve them, without the % sign and then sort them and add back the % sign. Below is is working and tested SQL example.

select concat(percentage, '%') -- add '%' sign
from (
select SUBSTRING_INDEX(percentage, '%', 1) percentage  -- remove `%`
from percentage_sorting) t
order by percentage;  -- order by values

Upvotes: 2

Álvaro González
Álvaro González

Reputation: 146460

Once you convert your percentages to strings you'll basically get alphabetical order, which is normally not what you want for numbers:

20%
2%
12%
1%

If you want numeric sort, you have to sort on actual numbers:

SELECT CONCAT(percentage, '%')
FROM data
ORDER BY percentage DESC

Upvotes: 0

Related Questions