Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4

From: Frank (fnoell_cisi_at_hotmail.com)
Date: 07/10/04 

Date: Sat, 10 Jul 2004 09:30:14 -0700

It's difficult to know what happens by locale, but this is not an issue for
me as my customer serves only Oregon. The knowledgebase article doesn't
mention locale. Apparently Microsoft decided to change the sort because of
index type of listings, and certainly it is easier to find items in an index
with this type of sort. It's their "resolution" that irritates me: "This
behavior is by design. You cannot change any settings to affect this
behavior."

To recap what Microsoft did: Pretend apostrophe, dash and underscore don't
exist, and sort like they aren't there.

I ended up doing a bubble-type sort on the key, and single inquiries
(SELECT) to address the records. As John stated, with 15 or less records,
it's not too big of a deal. There will be several occurrences of the "15 or
less records", but that's the way it goes.

For others who might find this, it appears that a solution might be to make
the sort field a key field in the Access/Jet database, and retrieve the
records without the ORDER BY statement. I discovered this by accidentally
leaving it out during testing. As the single lookups aren't really too big
of a deal for me, and I already had the bubble-type sort coded, I decided
not to pursue checking out the no ORDER BY method. I did find many
references that stated that the only way to insure a consistent order was to
use the ORDER BY statement, but, in the case of a key field, it may not be
the case. It is interesting, and plausible, that the key sequence would not
ignore the 3 characters like to ORDER BY clause. I have included this
section as I did not find any other possibility after spending quite a bit
of time looking, and it might help someone ;-).

Thanks for the comments.

Frank

Relevant Pages

  • Re: How do I alphabetize a column but also have the corresponding cells move with the sort?
    ... rows without repeating the name in the same row in column A would cause them ... to sort incorrectly. ... If you want to have multiple rows for a definition, ... you need to have a matching value in the key field. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How hash tables work
    ... Basically i need to sort my records by more than ... as a string or create an integer hash from fields. ... In my case it is possible to have duplicate hashes. ... >>So instead of doing a binary search on the first key field in the ...
    (alt.comp.lang.borland-delphi)
  • [OT] IDCAMS Error Counts?
    ... Originally the file was de-duped with a SORT on the key field and the SUM ... and the de-duping no longer occurs. ... REPRO'd there are records with duplicate key fields present and IDCAMS ...
    (comp.lang.cobol)
  • Re: How hash tables work
    ... Basically i need to sort my records by more than ... >one key field eg. ... code I hacked together to maintain multiple AVL tree indexes into ...
    (alt.comp.lang.borland-delphi)
  • Re: sort -m and locale?
    ... problem they messed up the month code in sort. ... months from the system using the locale information. ... The months from the system are in upper and lower ... In the end I put up a red herring for everyone to follow with the locale ...
    (comp.unix.shell)