SELECT DISTINCT statement suddenly not sorting automatically as per usual.

Posted on 2007-07-20
Medium Priority
Last Modified: 2012-06-27
I have an Access 2003 application that contains linked SQL2000 tables and a lot of VBA code automation.  I deployed it 2 weeks ago before going on vacation and when I returned yesterday was told that about 2 days ago (while I was still out) it had spontaneously begun losing some of the functionality and exhibiting odd quirks, among them a combo box for which the row source query is a SELECT DISTINCT statement had suddenly started showing up unsorted, which I have never seen before.  I can of course specify an ascending sort but have never before had to do so.  Nothing has changed with the back end SQL database or the Access interface.  The possible changes are automatic MS patches or possible local security changes  (we have been 'consolidated' and aren't usually notified of such changes anymore).  
Among a group of 5 users, these problems are not presenting in the same combinations from user to user and cropped up at different times from some users.  

In particular, can anyone think of what might have caused a SELECT DISTINCT result to suddenly stop sorting itself by default?  I'm wondering if something could be generally preventing Office2003/VBA/ODBC  from performing normally, and if I had an idea of what could be causing the un-sort, that would be a start.

Thanks for any ideas.  
Question by:jaw0807
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19533375
without ORDER BY , sorting is NOT guaranteed in any matter.
if you want the records sorted, use ORDER BY

the reason why DISTINT can, implicitely, sort the results is how it internally works.

Author Comment

ID: 19533600

As I say, I know that I can explicitly tell the select distinct query to sort.  What I have never seen is a select distinct result set, that had been reliably returning a sorted result for over a week without that explicit instruction, to suddenly (in the middle of a workday) start returning an unsorted result.  On another note, I have never personally seen a select distinct query NOT return a sorted result set, so this is very odd to my experience.

Let me re-emphasize that this question is more about ferreting out what could have caused this (and the other problems) to suddenly spring up in a seemingly random manner than it is about the select distinct list itself.   Again, we are no longer in control of (or necessarily notified of) changes to the user machines and I am trying to figure out what Unkowns could be at work here so that I can ask The New Overlords specific questions about what might have changed, since just asking them "what's changed" doesn't get a very useful result.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19533750
"what changed" , well, that is purely a matter of a threshold, as from when the engine processes a query in eventually a different way.
DISTINCT will internally create a temporary table of the values to be "DISTINCT", but as from a certain moment on, the way to do that might change a little bit.

Expert Comment

ID: 19535728
I had a similar problem when I upgraded an application from Access to SQL Server. In Access, GROUP BY would automatically sort. In SQL Server, you had to throw in an ORDER BY clause.

Expert Comment

ID: 21156936
Forced accept.

EE Admin

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question