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.

score 19 · Accepted Answer · answered Jan 27 '12 at 16:19

19

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)

answered Jan 27 '12 at 16:19

Justin

3,400
3
32
56

3

If you don't have an ArcInfo license then use this toolbox (tested on shapefiles only) http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=BDF21676-1422-2418-34E6-A29C6BAC8A0B – Jan 27 '12 at 18:39
3

Its amazing how many functions that once required an ArcInfo function can now be implemented using some fairly simple Python code/modules. It's great! – RyanKDalton Jan 29 '12 at 05:50
Thanks Dan, that did the trick. My license didn't support the Frequency toolbox. – Steve Jan 30 '12 at 14:32

tharen · Answer 2 · 2012-01-29T04:35:40.200

15

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))

edited Jan 29 '12 at 04:35

answered Jan 28 '12 at 00:49

tharen

996
8
16

1

Since you are only interested in one field, for better performance I would specify the optional fields argument, e.g. myList = set([row.getValue(fldName) for row in arcpy.SearchCursor(fcName, fields=fldName)]) – blah238 Jan 29 '12 at 01:03
Good idea. I'd be curious to test the performance difference. For SDE data sources it's likely significant, but for shapefiles and file geodatabases I'd be surprised if was appreciable. – tharen Jan 29 '12 at 04:21
This is great. I had a link that showed this nicely but it broke one day. Hope se will have this for a nice long time. – Justin Jan 29 '12 at 18:12
I used myList = list(set([row.getValue(fldName) for row in arcpy.SearchCursor(fcName, fields=fldName)])) to get this to perform as expected. – nofunsally Jan 07 '21 at 16:17

Brent Edwards · Answer 3 · 2012-01-27T16:56:01.083

4

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.

edited Jan 27 '12 at 16:56

answered Jan 27 '12 at 16:06

Brent Edwards

1,849
15
21

1

You can also use DISTINCT over Personal Geodatabase. – Jakub Sisak GeoGraphics Jan 27 '12 at 19:53

score 4 · Answer 4 · edited Dec 16 '15 at 19:11

4

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

edited Dec 16 '15 at 19:11

PolyGeo

65,136
29
109
338

answered Jan 29 '12 at 00:27

klewis

7,475
17
19

Thanks. This worked, but then I couldn't copy the list out of the window! – Steve Jan 30 '12 at 14:33
@steve see http://gis.stackexchange.com/questions/58939/copy-not-export-a-table-from-arcmap – matt wilkie Dec 17 '15 at 22:08

fallingdog · Answer 5 · 2016-09-13T20:44:05.367

1

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)

edited Sep 13 '16 at 20:44

answered Sep 13 '16 at 20:20

fallingdog

75
1
8

score 0 · Answer 6 · answered Jan 27 '12 at 16:41

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.

score 0 · Answer 7 · answered Jan 27 '12 at 18:10

0

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.

answered Jan 27 '12 at 18:10

Chad Cooper

12,714
4
46
87

2

"DISTINCT" only works with Personal GDB not File GDB. – Jakub Sisak GeoGraphics Jan 27 '12 at 19:50

score 0 · Answer 8 · answered Jan 27 '12 at 18:32

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.

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

8 Answers8

Linked