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.