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

  • define expensive? a house for sale is only expensive to the majority but not expensive to the millionaires/billionaires. – Mapperz Mar 9 '13 at 16:12
  • 1
    You are looking for a SQL solution but the SQL solution is not supported, therefore you are asking the impossible. Are you looking for non-SQL alternatives? – blah238 Mar 9 '13 at 18:17
  • I'd be happy with any elegant solution that can go in a model builder.... – goonerify Mar 9 '13 at 18:30

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  )
  • TOP and LIMIT are not supported in file geodatabases. It is implied (though to be fair, not stated explicitly) that this is the data source the OP is using. – blah238 Mar 9 '13 at 19:09
  • I'm using a personal geodatabase for this. This solution seems similar to the query that i included in the question as you rightly pointed out. I will give it a shot however (with the square brackets] – goonerify Mar 9 '13 at 19:15
  • You sir Mr Ako are a genius. I created a new geodatabase, exported the data and got it to work Thanks again. – goonerify Mar 9 '13 at 20:24
  • 1
    I really like this gis stackechange site – goonerify Mar 9 '13 at 20:24
  • 1
    It was supposed to be...set up by a team member and it never occured to me to check. Anyway...thanks for all your help, i really appreciate it – goonerify Mar 9 '13 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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