Select by Attributes on features sharing common value using ArcMap

Question

I am attempting to 'select by attributes' a point feature with the lowest numeric value which shares a common value with other point features in another field. For example: I may have 3 point features with address values: 123, 125 and 127. Each of these point features shares a parcel address value of 131 in another field.

I simply need to select the lowest address point for each parcel address so that I can use a definition query to display a subset of the data (Only points with these lowest values).

Answer 1

Use the Summary Statistics tool with the following parameters:

1. Input Table: your point feature class
2. Statistics Field: the address value field. Select MIN as the statistic type from the dropdown list.
3. Case Field: the parcel address field.

This will group records according to the Case Field, select the minimum address value and write this value to the new output table. You could then join this table to your original feature class and keep only matching records to display your "minimum" subset.

Answer 2

Try a definition query that uses this pattern:

select * from [YOUR FEATURE CLASS] where

F1 > 0 AND F1 in (select min("F1") from [YOUR FEATURE CLASS] where F2 in (select distinct F2 from [YOUR FEATURE CLASS]) group by F2 )

F1 - the field name with different values (123, 125 and 127)

F2 - the field name with the common value. (131)

There is a decent discussion of this in the 10.1 Help - search for "SQL reference for query expressions used in ArcGIS".

Works with an ArcSDE feature class. Haven't tried it with a file GDB. Arabella's answer should work with a file GDB.