Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Format values as Percent Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, August 26, 2008 3:29 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, November 11, 2009 8:59 AM Points: 14, 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
Post #558696
 Posted Tuesday, August 26, 2008 5:11 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, December 28, 2015 7:52 AM Points: 1,260, Visits: 3,509
 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/
Post #558737
 Posted Tuesday, August 26, 2008 5:30 AM
 SSC-Forever Group: General Forum Members Last Login: Saturday, December 3, 2016 5:18 AM Points: 45,619, Visits: 44,147
 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
Post #558753
 Posted Tuesday, August 26, 2008 5:46 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, December 28, 2015 7:52 AM Points: 1,260, Visits: 3,509
 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/
Post #558760
 Posted Tuesday, August 26, 2008 5:47 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, November 11, 2009 8:59 AM Points: 14, Visits: 63
 Well I am expecting result in following order:1.10%2.00%10.00%12.00%
Post #558762
 Posted Tuesday, August 26, 2008 5:51 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, December 28, 2015 7:52 AM Points: 1,260, Visits: 3,509
 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/
Post #558766
 Posted Tuesday, August 26, 2008 5:56 AM
 SSC-Forever Group: General Forum Members Last Login: Saturday, December 3, 2016 5:18 AM Points: 45,619, Visits: 44,147
 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
Post #558767
 Posted Tuesday, August 26, 2008 6:01 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, November 11, 2009 8:59 AM Points: 14, Visits: 63
 Thanks Gail! Its perfect.
Post #558772
 Posted Tuesday, August 26, 2008 6:04 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, December 28, 2015 7:52 AM Points: 1,260, Visits: 3,509
 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/
Post #558777
 Posted Thursday, August 28, 2008 11:14 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, November 11, 2009 8:59 AM Points: 14, 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
Post #560691

 Permissions