ACC: ORDER BYs Must Be Output Columns in DISTINCT Queries

Last reviewed: May 28, 1997
Article ID: Q96895
The information in this article applies to:

- Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

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

If you create a query and use the DISTINCT predicate in conjunction with the ORDER BY clause, the fields that are being ordered must be output columns. If the ORDER BY fields are not output columns, you may receive the following error message:

   ORDER BY clause ([Table Name].[Field Name]) conflicts with DISTINCT.

RESOLUTION

Use the DISTINCTROW predicate or remove the specified field from the ORDER BY clause.

MORE INFORMATION

Steps to Reproduce Behavior

Follow these steps in the sample database NorthWind (or NWIND.MDB in versions 1.x and 2.0):

  1. Create a new query and add the Customers table.

  2. Place the Address and City fields in the query design grid.

          Query: MyQuery
          ----------------------
          Field Name: Address
    
             Sort: Ascending
             Show: False
          Field Name: City
             Show: True
    
    

  3. On the View menu, click SQL. Modify the SQL statement as follows:

         SELECT DISTINCT Customers.City
         FROM Customers
         ORDER BY Customers.Address;
    

  4. Run the query. Note that you receive the following error message:

          ORDER BY clause (Customers.Address) conflicts with DISTINCT
    

To resolve this, you must Show the Address field or use DISTINCTROW in place of DISTINCT in the SQL statement.

REFERENCES

For more information about using SQL predicates, search for "DISTINCT," and then "ALL, DISTINCT, DISTINCTROW, TOP Predicates (SQL)" using the Microsoft Access for Windows 95 Help Index.


Keywords : kbusage QryOthr
Version : 1.0 1.1 2.0 7.0
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided


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.

Last reviewed: May 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.