English

How To: Select minimum and maximum values in the Select By Attributes dialog box

Summary

Instructions provided describe how to use subqueries in the SQL expression dialog box to access aggregate functions such as MIN and MAX. For example, write a query that selects the maximum date in a date field.

Use GROUP BY subquery clauses to return values from aggregate functions on sets of values.

Note:
Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Subqueries done on a versioned ArcSDE feature class that has been registered without the option to move edits to base will not return features stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.

Procedure

Use the following examples with Geodatabase and ArcSDE Feature Classes and Tables, as well as Access tables from an OLE DB Connection. File Geodatabases do not support any subqueries, including the GROUP BY clause.

For more information, please refer to the ArcGIS Desktop Help 9.3 - SQL Reference.

  • Select the record from the table with the most recent date, where [mydate] is the date-type field and table_name is the name of the table or feature class:

    Code:
    [mydate] in (SELECT max( [mydate] ) FROM table_name)

  • Select the record from the table with the oldest date, where [mydate] is the date-type field and table_name is the name of the table or feature class:

    Code:
    [mydate] in (SELECT min( [mydate] ) FROM table_name)

  • Select the records from the table with the most recent date for sets of values, where [groupID] is the field that contains the values that define the groups:

    Code:
    [mydate] in (SELECT max( [mydate] ) FROM table_name GROUP BY [groupID])

  • Select the records from the table with the oldest date for sets of values, where [groupID] is the field that contains the values that define the groups:

    Code:
    [mydate] in (SELECT min( [mydate] ) FROM table_name GROUP BY [groupID])