Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 order by with Case Statement

Author  Topic 

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-03-08 : 14:44:47
Hi friends

I have a nvarchar column called customercode with following data
1
1
2
2
8
10
9
10
I want to order it for my select statement and used the following sql statement :
Select top 200 Customercode from
Customer
where Customercode is not null
order by case IsNumeric(Customercode)
when 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode
else Customercode
end

It orders fine but it does not allow to choose distinct values ..How can i achieve it..Thank you

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-03-08 : 14:53:15
Thank You all but i resolved my issue..
quote:
Originally posted by akpaga

Hi friends

I have a nvarchar column called customercode with following data
1
1
2
2
8
10
9
10
I want to order it for my select statement and used the following sql statement :
Select top 200 Customercode from
Customer
where Customercode is not null
order by case IsNumeric(Customercode)
when 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode
else Customercode
end

It orders fine but it does not allow to choose distinct values ..How can i achieve it..Thank you



Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-03-08 : 14:57:10
If I understand, what about
Select top 200 Customercode 
case when IsNumeric(Customercode) = 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode
else Customercode end AS Customercode
from Customer
where Customercode is not null
case when IsNumeric(Customercode) = 1 then Replicate('0', 100 - Len(Customercode)) + CustomerCode else Customercode end = 'myvalue'
order by 1


djj
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-09 : 01:13:12
Just putting ISNUMERIC(CustomerCode) = 1 will not ensure it will contain only numeric data. ISNUMERIC returns 1 for some specific characters too

Check the below

SELECT ISNUMERIC(val)
FROM
(
SELECT '$' AS val UNION ALL
SELECT '12e34' UNION ALL
SELECT '2d4' UNION ALL
SELECT ',' UNION ALL
SELECT '.'
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -