Sort Problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Sort Problem

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-11 : 13:14:32

hi
in my table, i have a column with nvachar(20) but enter numeric value to it (1,2,3, ...).
my problem is that when i select from my table and sort with that column, it it does not sort correctly, it sorted like this :

Field1   Field2   Field3(nvarchar(20))
--------------------------------------
a1       b1       1
a2       b2       10
a3       b3       11
a4       b4       12

...

how to solve my problem and correct this problem ?
i want this result :

Field1   Field2   Field3(nvarchar(20))
--------------------------------------
a1       b1       1
a2       b2       2
a3       b3       3
a4       b4       4
...

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-11 : 13:20:16

Change the data type to int for field3.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 13:37:11

Cant really understand why you've column as varchar if you're storing only numeric value.

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-11 : 13:53:26

thanks for reply
but i don't want to change it to int, because in the future, maybe user enter numeric+alphabetic values like this :

m/123/a54

now, how to solve sorting problem ?
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 14:06:00

quote:
Originally posted by hdv212

thanks for reply
but i don't want to change it to int, because in the future, maybe user enter numeric+alphabetic values like this :
m/123/a54

now, how to solve sorting problem ?
thanks

ok. dont change it to int but just cast it for the timebeing to get your sorting work correctly

SELECT *
FROM YourTable
ORDER BY CAST(Field3 AS int)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 14:09:04

Please note that it will still break if you happen to have a non numeric data in your Field3. try this also and see if this works.This is not using any type of casting


SELECT *
FROM YourTable
ORDER BY LEN(Field3),Field3

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-11 : 14:25:54

Thanks visakh16
nonce it works.
thanks again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-12 : 03:07:45

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources