Select minimum and maximum values in the Select By Attributes window in ArcMap

Summary

Note:
This article applies to retired ArcGIS versions 9.x and 10.0. Later versions of ArcGIS may contain different functionality, as well as different names and locations for menus, commands and geoprocessing tools.

It is recommended to migrate to ArcGIS Pro. See See Migrate from ArcMap to ArcGIS Pro for more information.

The instructions provided describe how to use subqueries in the SQL expression of the Select By Attributes window to access aggregate functions such as MIN and MAX. For example, 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 non geodatabase file-based data sources do not support subqueries. Subqueries performed on a versioned ArcSDE feature class that has been registered without the option to move edits to base do not return features stored in the delta tables. File geodatabases provide limited support for subqueries explained in this article, while personal and ArcSDE geodatabases provide full support. Refer to the database management system (DBMS) documentation for information on the full set of subquery capabilities of personal and ArcSDE geodatabases.

Procedure

Use the following examples with geodatabase and ArcSDE feature classes and tables, as well as Access tables from an OLE DB Connection:

• Select the record from the table with the most recent date, where <date> is the date type field, and <table_name> is the name of the table or feature class:

<date> = (SELECT MAX(date) FROM <table_name>)

• Select the record from the table with the oldest date, where <date> is the date type field, and <table_name> is the name of the table or feature class:

<date> = (SELECT MIN(date) FROM <table_name>)

• Select the records from the table with the most recent date for sets of values, where <group_ID> is the field that contains the values that define the groups:

<date> = (SELECT MAX(date) FROM <table_name> GROUP BY <group_ID>)

• Select the records from the table with the oldest date for sets of values, where <group_ID> is the field that contains the values that define the groups:

<date> = (SELECT MIN(date) FROM <table_name> GROUP BY <group_ID>)

Note:
Query expressions in ArcGIS adhere to standard SQL expressions. The SQL syntax used in an expression differs depending on the data source. Each data source has its own variant of SQL, which are referred to as SQL dialects. See: SQL reference for query expressions used in ArcGIS