November 20, 2014 at 4:10 pm
Have a DB with Collation: SQL_Latin1_General_CP1_CI_AS
create table testtable (c nvarchar(1) null)
insert into testtable (c) values('8')
insert into testtable (c) values('9')
insert into testtable (c) values(':')
insert into testtable (c) values(';')
select c, ASCII(c) ascvalue from testtable order by c
[p]
get the following results:
c | ascvalue
------------
: | 58
; | 59
8 | 56
9 |57
[/p]
Why does SQL server sort the order contrary to ascii code order?
Cullen
Dare to Question -- Care to Answer
Time is like water in a sponge, as long as you are willing you can always squeeze some.
--Lu Xun, Father of Modern Chinese Literature
November 20, 2014 at 5:25 pm
Because it doesn't. Please find me your documentation that states ORDER BY with your collation will follow ASCII order.
create table testtable2 (n tinyint not null, c varchar(1) not null)
GO
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L3)
insert testtable2
SELECT n, CHAR(n) FROM Nums WHERE n <= 255;
(255 row(s) affected)
select N, c, row_number() OVER (ORDER BY c) as ordernum
from testtable2
order by c
--don't forget to clean up tables here...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 21, 2014 at 7:02 am
To clarify, by 'contrary ascii code', I meant not respecting the ASCII binary values, which is demonstrated by my code snippet so does yours. Thanks for the confirmation!
Cullen
Dare to Question -- Care to Answer
Time is like water in a sponge, as long as you are willing you can always squeeze some.
--Lu Xun, Father of Modern Chinese Literature
November 24, 2014 at 3:32 am
database sorting is depend on datatype of the table column.
regards
Rajani Karthik
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply