Alphanumeric Sort Issue

[rustyd]

Converting to VB 6 Applications connecting to Access to .NET connecting to SQL Server. I'm comparing reports and noticed an inconsistancy in the order some records were displayed.

 

The Short Story

In Access DAO recordsets, the character "-" doesn't appear to be affecting the sort of a field. I have records with the first 13 characters exactly the same (including case). The next letter in 1 is a "." (period). The next letter in another is a dash ("-"). The ASCII value of the period is 45. The ASCII value of the dash is 46. Here are two strings that I'm sorting:

 

My Company Co.

My Company Co-Ames

 

In VisData connecting to the Access database, this shows in the correct order:

 

SortMail

-----------------------------------

My Company Co-Ames

My Company Co.

 

In the Access recordset in VB 6, they sort like this:

 

SortMail

-----------------------------------

My Company Co.

My Company Co-Ames

 

In Query Analyzer and .NET datareader, they sort like this:

SortMail

-----------------------------------

My Company Co-Ames

My Company Co.

 

I attached an Access Database (Office XP) with an example of this. I have a table with 3 fields and a query showing the results that I'm talking about. I entered about 14 records with a period or a dash.

 

This is not the version of Access we use with our VB 6 app. We use '97 or 2000.

 

Is there a know problem or a fix to this?

db1.zip

[Nerseus]

I'm confused what the problem is...

First, you have the ASCII values backwards: a dash is 45 a period is 46.

 

Based on ascii codes, I'd expect the dash to sort first assuming an Order By with "ASC" for ascending. This is what you said VisData does and is correct. This is also what you said .NET does - doesn't that mean it's correct?

 

Or, are you asking to fix the ADO Recordset's sort, which appears to be off?

 

-nerseus

[rustyd]

Oops! Ascii values were backwards :o

 

Based on ascii codes, I'd expect the dash to sort first assuming an Order By with "ASC" for ascending. This is what you said VisData does and is correct. This is also what you said .NET does - doesn't that mean it's correct?

[/Quote]

 

Based on ascii codes, I expect the dash before the period. The .NET datareader and VIS Data are sorting correctly (at least as I would expect with the dash before the period). The Access query in the example database I attached doesn't sort correctly (appears to ignore the dash) and the DAO recordset does the same thing. I imported that table into SQL Server and it doesn't sort correctly in Query Analyzer.

 

The problem is I'm getting different results from the same SQL statement depending on how it was run.

[Nerseus]

Just to be clear:

When you run your queries, are you using an ORDER BY clause? If you're just selecting rows or viewing them in Access's table view, there's no guarantee on how those records will sort.

 

-ner

[rustyd]

Order By

 

I am using a Query with an order by. I am including the example table data I created for further reference.

 

Table Name: cm

Field1: Account (int, primary key ASC)

Field2: MailName (Varchar 50)

Field3: SortMail (Varchar 50, Index ASC)

 

When I do a select with no order by, it uses the primary key index, which sorts by Account ASC.

 

select * from cm
Results:
Account     MailName                                           SortMail                                           
----------- -------------------------------------------------- -------------------------------------------------- 
1           123456789.                                         123456789.
2           123456789-                                         123456789-
3           123-456                                            123-456
4           123.456                                            123.456
5           My Company Co.                                     My Company Co.
6           My Company Co-Ames                                 My Company Co-Ames
7           My Company Co. Ames                                My Company Co. Ames
8           My Company Co-Ames                                 My Company Co-Ames
9           My Company Co.                                     My Company Co.
10          -123                                               -123
11          .123                                               .123
12          -123                                               -123

(12 row(s) affected)

 

When I use the SortMail column in the order by, the dashes should be sorted before the period.

select * from cm order by sortmail

Results:
Account     MailName                                           SortMail                                           
----------- -------------------------------------------------- -------------------------------------------------- 
11          .123                                               .123
12          -123                                               -123
10          -123                                               -123
4           123.456                                            123.456
3           123-456                                            123-456
2           123456789-                                         123456789-
1           123456789.                                         123456789.
5           My Company Co.                                     My Company Co.
9           My Company Co.                                     My Company Co.
7           My Company Co. Ames                                My Company Co. Ames
8           My Company Co-Ames                                 My Company Co-Ames
6           My Company Co-Ames                                 My Company Co-Ames

Edited August 12, 2004 by rustyd

[PlausiblyDamp]

Do you happen to know what collation order the SQL server is configured to use? Not all languages sort based on the raw ascii values - often different collation orders will have differerent ordering for punctuation / extended characters.

In SQL 2000 if you go to the table designer you can configure the collation on a column by column basis if required - it may be worth seeing if changing the collation affects the results.

[rustyd]

collation settings

 

sp_helpsort

 

returned

Server default collation                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

 

The collation order applies to SQL Server, so if I'm using the same SQL statement from a DataReader or QueryAnalyzer, I should get the same results. That is what is puzzling me, unless the DataReader specifies its own collation on the SELECT statement based on the locale setting when setting up .NET (guessing).

 

I just setup a datareader sample solution to show what I'm talking about, but it showed the same results as the QueryAnalyzer.

 

I attached a picture of the form using the datareader. The first picture (sortorder.gif) is listbox1.sorted = false, and the second picture (sortorder2.gif) is listbox1.sorted = true (this one is strange).

Edited August 12, 2004 by rustyd

[PlausiblyDamp]

If you try changing from a specified collation to binary it will probably work correctly - however it will also treat upper and lower case characters as being different i.e. things begining with a - z would come after A - Z, probably not what is desired. It looks as though the ordering is being affected by the characters that come after the - or .

Not too sure what the exact deal is with the collation orders but there does appear to be a certain logic to it.

[rustyd]

thanks for the help. Definitely don't want to change to binary sort.

[PlausiblyDamp]

The second one is just doing a simple string sort on the items (look at the 1st column and you will see what has happened).

I must admit I found the way the sort to be curious - although I would tend to ignore it in this case unless it was causing any particular problems....

[Nerseus]

I created a table just like yours (3 columns, same types, etc.) and put in the same data as you but I get a different sort. Mine shows what I think you want:

select * from cm order by sortmail

Account     MailName                                           SortMail                                           
----------- -------------------------------------------------- -------------------------------------------------- 
        10 -123                                               -123
        12 -123                                               -123
        11 .123                                               .123
         3 123-456                                            123-456
         4 123.456                                            123.456
         2 123456789-                                         123456789-
         1 123456789.                                         123456789.
         6 My Company Co-Ames                                 My Company Co-Ames
         8 My Company Co-Ames                                 My Company Co-Ames
         5 My Company Co.                                     My Company Co.
         9 My Company Co.                                     My Company Co.
         7 My Company Co. Ames                                My Company Co. Ames

 

In other words, dashes before periods in every case. This was on a default SQL Server 2000 install with a table created through the GUI (default collation).

 

You can also specify a collation per column - if you use Enterprise manager, go to design table and click on each varchar column. It should say "<database default>". I think if you import tables through DTS that it assigns a different collation. Might be worth checking...

 

-ner