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.
| Author |
Topic |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
 Posted - 2014-03-11 : 13:16:54
|
| Hi Friends,I have a table with customerID field stored as nvarcharSo I was using the following query for my purpose which worked well select distinct CAST(customerid as int) as custid from Customer_Data where Customer_Desktop is not null order by CAST(customerid as int)But now a new customer id is being stored as : 0333 and with my query the is leading 0 is being truncated...How can i now show the customerid with the leading 0 and same time order the query.Thank You |
|
|
sqlsaga
Yak Posting Veteran
93 Posts |
Posted - 2014-03-11 : 13:31:42
|
| Don't Convert it into INT.. that will solve the issue... If you have Distinct it will automatically use Order by so there is no need to an Order by exclusively again...Visit www.sqlsaga.com for more t-sql snippets and BI related how to's. |
 |
|
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2014-03-11 : 13:40:43
|
| Thank You SqlSaga for the response but that trick did not work in ordering. |
|
|
|
sqlsaga
Yak Posting Veteran
93 Posts |
Posted - 2014-03-11 : 14:10:41
|
| can you post some sample data and then let us know what the issue is?Visit www.sqlsaga.com for more t-sql snippets and BI related how to's. |
|
|
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2014-03-11 : 14:48:27
|
quote:
Originally posted by sqlsaga
can you post some sample data and then let us know what the issue is?Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.
This is how i resolved..The time crunch did not make me think properly..Glad i resolved on my own. select CustomerID from (select distinct CustomerID from Customerdata where CustomerID is not null) t order by cast( CustomerID as int) |
|
|
|
sqlsaga
Yak Posting Veteran
93 Posts |
Posted - 2014-03-11 : 15:42:06
|
| Why do you want to use a subquery... try like this directly in that case...select distinct CustomerIDfrom Customerdatawhere CustomerID is not nullorder by cast( CustomerID as int)Visit www.sqlsaga.com for more t-sql snippets and BI related how to's. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2014-03-17 : 08:12:41
|
| orselect distinct CustomerIDfrom Customerdatawhere CustomerID is not nullorder by CustomerID+0MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
ordering problem with numeric stored as nvarchar?