Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

Thanks for your help.

share|improve this question

7 Answers 7

up vote 10 down vote accepted

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)
share|improve this answer
2  
If you don't have an ArcInfo license then use this toolbox (tested on shapefiles only) resources.arcgis.com/gallery/file/geoprocessing/… –  Dan Patterson Jan 27 '12 at 18:39
1  
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! –  RyanDalton Jan 29 '12 at 5:50
    
Thanks Dan, that did the trick. My license didn't support the Frequency toolbox. –  Steve Jan 30 '12 at 14:32

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))
share|improve this answer
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 1: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 4: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

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.

share|improve this answer
1  
You can also use DISTINCT over Personal Geodatabase. –  Jakub Jan 27 '12 at 19:53

Using an ArcView license, can't you open the Table view, right-click a column, select Summarize?

share|improve this answer
    
Thanks. This worked, but then I couldn't copy the list out of the window! –  Steve Jan 30 '12 at 14:33

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.

share|improve this answer

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.

share|improve this answer
    
"DISTINCT" only works with Personal GDB not File GDB. –  Jakub Jan 27 '12 at 19:50

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.