English
Continue in the app
Be The First To Get Support Updates
Want to know about the latest technical content and software updates?

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

Summary

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. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to the database management system (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:

Note:
File geodatabases do not support any subqueries, including the GROUP BY clause. Refer to ArcMap: SQL reference for query expressions used in ArcGIS for more information.
  • 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>)

Related Information

Last Published: 6/17/2021

Article ID: 000008936

Software: ArcGIS-ArcEditor 9.3.1, 9.3, 9.2, 9.1, 9.0, 10 ArcGIS-ArcInfo 9.3.1, 9.3, 9.2, 9.1, 9.0, 10 ArcGIS-ArcView 9.3.1, 9.3, 9.2, 9.1, 9.0, 10