ArcGIS Desktop – Using SELECT TOP Query Effectively

arcgis-desktopselect-by-attributesql

Is there a way to use a SELECT TOP (n) within ArcGIS desktop? Is appears that since the ArcGIS query builder is effectively building the SQL statement after the WHERE clause, that this may not be possible.

Here is a typical SQL example of usage:

SELECT TOP 3 * FROM Layer

Best Answer

If your underlying database supports sub queries, you can build a query with a TOP N that returns ObjectIds (or any other field) and use that in your definition query.

OBJECTID IN (*query here*)

For example, here's a SQL Server query for a TOP 20, random selection: enter image description here

The above query will return 20 different rows each time the map or attribute table is refreshed. This also could come in handy for an ArcMap practical joke for less experienced users (April Fools is less than a month away).

Of course, the sub query doesn't have to be a TOP N query nor do you need to use a unique id. Maybe work up a query to find duplicates:

FIRE_ID IN (SELECT FIRE_ID
FROM [SandboxGeodb].[dbo].[TESTFEATURECLASS] GROUP BY FIRE_ID HAVING COUNT(FIRE_ID) > 1)

Related Question