SQL – Selecting Top 5 Most Expensive Items in Personal Geodatabase

arcgis-desktoparcmappersonal-geodatabaseselectsql

I'd like to know what is the best way to select the 5 most expensive items (in terms of actual monetary value) in my personal geodatabase where i have a price field. Prices are not necessarily ordered but can be. I'm looking for an elegant SQL solution for this, but currently it seems TOP and LIMIT are not supported in ArcGIS 10 and there is no specific tool to limit your search to just 5. My solution would be to use the IN and TOP 5 keywords like so

SELECT * FROM myTable WHERE itemPrice IN (SELECT TOP 5 "itemPrice" FROM myTable)

I get an error about the SQL syntax, but when i hardcode the values for the top 5 most expensive the query works i.e.

SELECT * FROM myTable WHERE itemPrice IN (100, 105, 110, 125, 150)

I am trying to use the query builder to create a model in ArcMap that will select only the five most expensive items in the attribute table of a feature class.

Best Answer

You can do something like this in the query builder--it looks much like what you already have--this is tried on a personal geodabase "backend"--each has its own sql flavor so the file geodabase may be different. The personal geodatabase requires brackets for field names.

It is not fool proof, though. In my case here I select top five census tracts by population, but if two tracts had the same value in the top five list, I would get six tracts returned.

[Population] in(SELECT TOP 5 ([Population]) 
FROM SanFrancisco_CensusTracts_part1 
ORDER BY [Population] DESC  )
Related Question