SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 order by with Case Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
313 Posts

Posted - 03/08/2013 :  14:44:47  Show Profile  Reply with Quote
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

USA
313 Posts

Posted - 03/08/2013 :  14:53:15  Show Profile  Reply with Quote
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

USA
338 Posts

Posted - 03/08/2013 :  14:57:10  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/09/2013 :  01:13:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000