knox knox - 6 months ago 47
SQL Question

Wrong order of sorting in field list

Data structure looks like:

ID | Name | CID
---------------------------------
1 | x |
2 | a | 12
3 | a | 12
4 | c |
5 | x |
6 | [ |


When I perform the ordinary queries

SELECT DISTINCT Name FROM table WHERE ISNull(CID_ID) ORDER BY Name; [1]

SELECT DISTINCT Name FROM table ORDER BY Name; [2]


I get the expected result for [1] in the MySQL CLI:

| Name
------------
| c
| x
| [


And for query [2]:

| Name
------------
| a
| c
| x
| [





So my problem is: When I open the recordset and bind it to a field list, the order of the items is messed up. In detail, the dataset with bracket value "[" is placed on the top of the field list in the MS-Access form, but only when i perform query [1].

I get the NOT the expected result for [1]:

| Name
------------
| [
| c
| x


And for query [2]:

| Name
------------
| a
| c
| x
| [


The simplified VBA code snippet looks like this:

Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCT Name FROM table WHERE ISNull(CID_ID) ORDER BY Name;"
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)


After i catched the recordset, i override the recordset of the list:

Set Me.ListNames.Recordset = oiManager.LoadDistinctName()





Additional information (1):

Server version: 5.6.16 MySQL Community Server (GPL)




Additional information (2):

| Name | Engine | Version | Collation
| ########## | InnoDB | 10 | utf8_general_ci





Additional information (3):
So I created two separate queries in access and fired them up. In the datasheet view i get the "swapped result": for query [1] it's on the top, for query [2] the bracket is on the bottom. It's an linked MySQL table with ODBC connection. I played around with some of the table properties but with no effect.

Then I created a dummy table in access itself, filled the table with some values and the result was consistent: both times displayed on the top. So in my opinion, the inconsistency must base upon the fact that I'm using a linked MySQL table.

I'm using:


  • Microsoft Access 2013 (9.0.24172.951362)

  • ODBC-Driver 32-Bit, MySQL ODBC 5.2 Unicode Driver


Answer

I was able to recreate your results. The change in sorting behaviour appears to be related to using the IsNull() function. When I change query [1] to

SELECT DISTINCT [Name] FROM [table] WHERE [CID_ID] IS NULL ORDER BY [Name];

then the ordering is consistent for both query [1] and query [2] (and is the same as the MySQL queries).

My guess is that the revised query is "plain" SQL and gets handed to the ODBC driver directly, while the original IsNull() query required that the ACE/Jet Expression Service become involved, and consequently the final sorting was the way Access normally does it for queries against native ACE/Jet tables.