Select distinct values from a single column of an attribute table (or layer)

Question

Is there a way to select distinct values from a column in ArcMap? I have the data in both GDB and SHP formats. I have searched for ways to select using SQL, QueryLayers, ModelBuilder and individual toolboxes and it appears as all selection options always SELECT * FROM tableName WHERE ...

In SQL I would write SELECT DISTINCT columnName FROM tableName.

Answer 1

Or you can run the ArcToolBox tool Frequency (Analysis Tools>>Statistics>>Frequency) which will output a table with unique values and a count of how many time they appear.

Or you could write python script that gets a SearchCursor on a field then build a list of all values of the form

if value not in myList:
    myList.append(value)

Answer 2

Use a Python list comprehension.

import arcpy
fldName = 'val_fld'
fcName = 'feature_class.shp'
#set creates a unique value iterator from the value field
myList = set([row.getValue(fldName) for row in arcpy.SearchCursor(fcName)]) 

For large datasets a memory efficient method would be to use a generator expression.

myList = set((row.getValue(fldName) for row in arcpy.SearchCursor(fcName,fields=fldName))

Answer 3

If your data is in PGDB format, you can do the following within the query builder dialogs (definition query, select by attributes, toolbox expressions etc.) using a subquery:

SELECT * FROM tableName WHERE ...

column_to_test_for_unique_values IN 
(SELECT column_to_test_for_unique_values
FROM table_name
GROUP BY column_to_test_for_unique_values HAVING
Count(column_to_test_for_unique_values)=1)

This will return the records for which the values in the column_to_test_for_unique_values are unique.

Answer 4

If you only have a Basic (formerly called ArcView) license, you could open the Table view, right-click a column, and select Summarize.

Answer 5

If your data is in a SDE (spatial database engine) one can use python and arcpy's ArcSDESQLExecute object. One can pass complex sql using this "method."

# set up executor for sql
executor = arcpy.ArcSDESQLExecute(sde_connection_sql)

# sql statements
select_max = """select max(LOAD_ID) from SDE.FEATURE_CLASS"""

# get load id
result_max_id = executor.execute(select_max)

Answer 6

Or use your Python script to export to CSV and then use the Python API of another database (say SpatiaLite) to read the csv and do a proper SQL query on it from within the same script. For a large table this might be a tiny bit quicker than rolling your own list builder - dunno.

However you do it, this is still a really annoying "feature" of ArcGIS.

Answer 7

What about using a distinct in a subquery (following is on a FGDB featureclass):

"STATE_NAME" = (select distinct "STATE_NAME" from EsriUsaCountiesDetailed)

Note from the help (10.0) that this does have limitations:

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries.

Answer 8

As Justin suggests. I normally do a summary on the field I want, then do a select distinct on the dbf run a little calculation to categorize each distinct value then join that back to the original.
It is the long way around, and you have to fenagel with your favorite caclulation methods. but...
Whatever gets the job done.