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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
331 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
331 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
352 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
52326 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  
 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