August 26, 2008 at 3:30 am
Hi ALL,
SQL:
Select MyColumn FROM
(
SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
) MyTable
Order by 1 asc
Result:
MyColumn
1.10%
10.00%
12.00%
2.00%
Problem: values are not sorted
Question: Is it possible to sort values in ascending Order?
Thanks
August 26, 2008 at 5:11 am
August 26, 2008 at 5:30 am
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 BaseColumn
Union
SELECT 0.011 as BaseColumn
Union
SELECT 0.02 as BaseColumn
Union
SELECT 0.12 as BaseColumn
) MyTable
Order by BaseColumn asc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 5:46 am
Gila you are very SQL Sick... very nice solution without adding or removing any other expression!
Nice solution Gila!
Dugi
:w00t:
August 26, 2008 at 5:47 am
Well I am expecting result in following order:
1.10%
2.00%
10.00%
12.00%
August 26, 2008 at 5:51 am
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 MyColumn
Union
SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc
August 26, 2008 at 5:56 am
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 Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 6:01 am
Thanks Gail!
Its perfect.
August 26, 2008 at 6:04 am
SMAZ (8/26/2008)
Thanks Gail!Its perfect.
He is perfect also in other solutions as I can see ...!
:hehe::hehe::hehe:
August 28, 2008 at 11:14 am
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
August 28, 2008 at 7:13 pm
Hi there,
If your already using the code given byu Gila Monster (:DYOUR THE MAN!), you can add this...
Yeah I know the code I added looks icky but hope it helps
Select NewColumn=CASE
WHEN CAST(LEFT(MyColumn,Len(MyColumn)-1)AS DECIMAL(5,2))<10 THEN '0'+CAST(MyColumn AS VARCHAR(50))
ELSE MyColumn
END
FROM
(
SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 28, 2008 at 7:25 pm
Hi there... agian,
Remember the icky code I added to Gila Monster and Dugi's code? We'll I made it a less icky but if your already using Gila's code, you need a few modifications for this one
Select NewColumn=CASE
WHEN MyColumn<10 THEN '0'+CAST(MyColumn AS VARCHAR(50))+'%'
ELSE CAST(MyColumn AS VARCHAR(50)) +'%'
END
FROM
(
SELECT CAST(0.10*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.011*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.02*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.12*100 AS numeric(10,2)) as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc
Oh... and sorry for my bad English, Hope my code also helps ^__^
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 28, 2008 at 7:38 pm
Hi there... again,
Heres another less icky way
Select RIGHT('00'+CAST(MyColumn AS VARCHAR(50))+'%',6)
FROM
(
SELECT CAST(0.10*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.011*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.02*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.12*100 AS numeric(10,2)) as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc
Although if you use this code, make sure that you won't have a data with0 '100%' or 1.00 casue that would ruuin the could like so
Select RIGHT('00'+CAST(MyColumn AS VARCHAR(50))+'%',6)
FROM
(
SELECT CAST(1.00*100 AS numeric(10,2)) as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc
Hope this also helps
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 29, 2008 at 5:57 am
Quatrei.X (8/28/2008)
Hi there,If your already using the code given byu Gila Monster (:DYOUR THE MAN!), you can add this...
By the way, Gila Monster is female !!!
September 12, 2008 at 1:52 am
AAAAAAAAW... Sorry about that gila. Didn't see the picture perfectly
sorry for bad english
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply