## Format values as Percent

 Author Message SMAZ-429882 SSC Veteran Group: General Forum Members Points: 272 Visits: 63 Hi ALL,SQL: Select MyColumn FROM (SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnion SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnion SELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnion SELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn) MyTableOrder by 1 ascResult:MyColumn1.10%10.00%12.00%2.00%Problem: values are not sortedQuestion: Is it possible to sort values in ascending Order?Thanks Dugi SSChampion Group: General Forum Members Points: 10158 Visits: 3511 It is interesting it doesn't work with ROW_NUMBER() function , so how it works on the other way!? ============================================================SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =http://www.sqlservercentral.com/articles/Best+Practices/61537/ GilaMonster SSC Guru Group: General Forum Members Points: 549587 Visits: 47743 They are sorted, they're just sorted as strings which is what the column is by the point the order by is evaluated. Perhaps something like this:`Select CAST(CAST(BaseColumn*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn FROM(SELECT 0.10 as BaseColumnUnionSELECT 0.011 as BaseColumnUnionSELECT 0.02 as BaseColumnUnionSELECT 0.12 as BaseColumn) MyTableOrder by BaseColumn asc` Gail ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass Dugi SSChampion Group: General Forum Members Points: 10158 Visits: 3511 Gila you are very SQL Sick... very nice solution without adding or removing any other expression!Nice solution Gila!Dugi ============================================================SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =http://www.sqlservercentral.com/articles/Best+Practices/61537/ SMAZ-429882 SSC Veteran Group: General Forum Members Points: 272 Visits: 63 Well I am expecting result in following order:1.10%2.00%10.00%12.00% Dugi SSChampion Group: General Forum Members Points: 10158 Visits: 3511 I find it on another way like this but Gila's post is the best for me!?`Select MyColumn FROM(SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnionSELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnionSELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnionSELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn) MyTableOrder by CAST(Replace(MyColumn,'%','') as DECIMAL) asc` ============================================================SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =http://www.sqlservercentral.com/articles/Best+Practices/61537/ GilaMonster SSC Guru Group: General Forum Members Points: 549587 Visits: 47743 SMAZ (8/26/2008)Well I am expecting result in following order:1.10%2.00%10.00%12.00%Which is exactly the order my query returns them in. Did you try it? Gail ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass SMAZ-429882 SSC Veteran Group: General Forum Members Points: 272 Visits: 63 Thanks Gail! Its perfect. Dugi SSChampion Group: General Forum Members Points: 10158 Visits: 3511 SMAZ (8/26/2008)Thanks Gail! Its perfect.He is perfect also in other solutions as I can see ...! ============================================================SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =http://www.sqlservercentral.com/articles/Best+Practices/61537/ SMAZ-429882 SSC Veteran Group: General Forum Members Points: 272 Visits: 63 One more querstion on thsi:Now the result is as follow:1.10%2.00%10.00%12.00%Is it possible to implement some formatting on that values like:1.10% = 01.10%2.00% = 02.00%10.00% = 10.00%12.00% = 12.00%Thanks