PRA: Ordinal Number in SQL ORDER BY Clause Ignored

Article ID: Q123709
Creation Date: 07-DEC-1994
Revision Date: 19-SEP-1996

The information in this article applies to:

Microsoft Access version 2.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you order the data in a query using an ordinal number in the query's SQL ORDER BY clause, the ORDER BY clause is ignored. The data is not ordered, and no error message is generated.

CAUSE

The Microsoft Jet database engine version 2.0 does not support the ordering of data by ordinal numbers in a SQL ORDER BY clause.

RESOLUTION

The Microsoft Jet database engine version 2.5 does support the ordering of data by ordinal numbers in a SQL ORDER BY clause.

STATUS

This problem no longer occurs with the Microsoft Jet database engine version 2.5, which is available with the Microsoft Access version 2.0 Service Pack. For information about how to obtain the Service Pack, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q122927
   TITLE     : WX1124: Microsoft Access Version 2.0 Service Pack

MORE INFORMATION

Ordering data by ordinal numbers refers to ordering data using a numeric reference in the SQL ORDER BY clause, rather than by a field reference. For example, in the sample query

   SELECT DISTINCTROW Table1.Names, Table1.Numbers
   FROM Table1
   ORDER BY Table1.Names DESC;

replace the field reference in the ORDER BY clause with:

   ORDER BY 1 DESC;

The number in the ORDER BY clause refers to the order of the fields in the SELECT statement. In the example above, 1 refers to the first field, Names.

Steps to Reproduce Problem

Start Microsoft Access and open the sample database NWIND.MDB.

Create a new query based on the Employees table.

Drag the First Name and Last Name fields from the field list to the query grid.

From the View menu, choose SQL.

Change the SQL statement so that it reads as follows:

      SELECT DISTINCTROW Employees.[First Name], Employees.[Last Name]
      FROM Employees
      ORDER BY 1 DESC;

Run the query. Note that the data is not sorted on the First Name field as it should be.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.