This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / A sort question regarding SQL Server. Thanks.If we have values in a varchar like
S1
S2
S9
S10
S12
S20
And we sort them Ascending, we get:
S1
S10
S12
S2
S20
S9
Is this a collation issue?
Can we make it sort to be like the following with a collation change?
S1
S2
S9
S10
S12
S20
Or, is there any good solution to work around this?
-brother-pooh(Pooh哥哥);
2007-6-28
{321}
(#3771409@0)
-
you entered data wrong. character var with numbers shall patch 0. S1 shall be entered as S01. change your data. that's the best solution.
-23456789(大白呼);
2007-6-28
(#3772375@0)
-
easy. select * from table1 order by CAST( RIGHT(col1, 1) as int)
-digitworm(digitworm);
2007-6-29
(#3773170@0)
-
This hurts performance for database has to do full scan.
-liquid(avaya);
2007-6-29
(#3773236@0)
-
so what's your answer?
-digitworm(digitworm);
2007-6-29
(#3774159@0)
-
I don't have answer, your way works if the query doesn't involve large amount of records. The better way is to review the table design and do proper indexing.
-liquid(avaya);
2007-6-29
(#3774429@0)
-
it depends. if LZ have permission and time to redesign, your idea is good. otherwise my simple query is right one.
-digitworm(digitworm);
2007-6-29
(#3774656@0)
-
That is wrong.
Should be
select * from table1
order by CAST(substring(col1,2,len(col1)-1) AS INT)
-hieveryone(:-));
2007-7-2
(#3778512@0)