The information in this article applies to:
SUMMARYThe information in this article applies to ODBC Desktop Database Drivers version 2.0. A SELECT clause that has the DISTINCT modifier in the select list and has an ORDER BY and the ORDER BY clause has columns that are not present in the select list, causes an error due to ambiguous semantics. The article discusses why this occurs. MORE INFORMATION
ODBC Desktop Database drivers are single-tier drivers. They are written as
a thin layer on top of the JET database engine. The JET engine is the SQL
engine that Microsoft Access and Microsoft Visual Basic use to get to
dBASE, Paradox, FoxPro, Btrieve, Access, Excel, and Text file formats.
is a valid SQL statement. On this statement, the engine does a sort on
the result set and throws out the columns that are not selected.
However, the semantics of a statement of the form
are non-deterministic. The engine does an implicit sort for the SELECT
DISTINCT. If it has the pairs (x0,y1),(x0,y2), one of them has to go
because of the DISTINCT x. But which one should go?
The decision that the engine makes will affect where in the sort order (x0) appears, because the sort for the ORDER BY clause is always done last. Because of the non-determinism in the semantics of a SELECT statement, when you try to execute it, you will generate the error: Note that what you want might be obtained by the following query:
This will cause duplicate values of x that you would not otherwise see had
you not included y in the select list. However, in most cases, this is a
very acceptable workaround.
Additional query words: 2.00.2317 dBASE Paradox Btrieve Access FoxPro Text Excel SQL 3015 Windows NT
Keywords : |
Last Reviewed: August 25, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |