Sorting Weirdness - 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 2000 Forums

Transact-SQL (2000)

Sorting Weirdness

Author

Topic

DanW
Starting Member

2 Posts

Posted - 2006-07-19 : 23:57:43

I recently ran into a strange sorting issue. If you create a table and populate it with data with the following script the results of a query against it are just not right.

CREATE TABLE MySortTable
(DictionarySort nvarchar(10) COLLATE Latin1_General_CI_AS NULL)
GO
INSERT MySortTable
VALUES ('Alpha')
INSERT MySortTable
VALUES ('Bravo')
INSERT MySortTable
VALUES ('Charlie')
INSERT MySortTable
VALUES ('alpha')
INSERT MySortTable
VALUES ('bravo')
INSERT MySortTable
VALUES ('charlie')
GO

The query and results looks like this:

SELECT DictionarySort FROM MySortTable
ORDER BY DictionarySort

Alpha
alpha
bravo
Bravo
Charlie
charlie

Notice that the lower case b comes before the upper case B. What's up with that!!!?

Life's tough. It's tougher when you're stupid

DanW
Starting Member

2 Posts

Posted - 2006-07-20 : 00:22:36

Figured it out with the help of Tom Cooper.

Case insensitive collation doesn't guarantee the order because it is case insensitive. It is still stange that the order is consistent except for the B, but oh, well!

Life's tough. It's tougher when you're stupid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-20 : 03:35:56

SELECT DictionarySort FROM MySortTable
ORDER BY DictionarySort COLLATE Latin1_General_CS_AS

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources