CODEWITHSUNDEEP
sqlpercentage
Glenn Low
Glenn Low

Reputation: 143

SQL how to sort column by percentages?

Hi I'm trying to sort in sql a column of percentages, but I'm unable to bring '100%' from the bottom to the top result, anyone has a solution to this?

I was using concat(x/y*100,'%') as percentage function, which gave me varying percentages e.g. 50%, 60%, 99%, 100%.

However an order by percentage desc then gives me the following order: 99% 60% 50% 100%

Thanks

Upvotes: 5

Views: 5122

Answers (2)

John Woo
John Woo

Reputation: 263703

Add this in your order by clause

SELECT  ...
FROM    ...
WHERE   ...
ORDER BY (x/y*100) ASC

The reason why ORDER BY percentage DESC doesn't work is because percentage column is a string and not a numeric.

Upvotes: 7

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

You should be ordering by x/y, it will give you the correct sort order. Use your expression that multiplies by 100 and adds a percentage sign to the end to format the output, not for sorting.

Upvotes: 1

Related Questions