3

I'd like to know what is the best way to select a limited top 'n' of rows from a specific column of a geodatabase file. Currently I use a function in the query builder of the select tool (arcgis10) in model builder which look a lot like the one posted on another item on this site (Select top 5 most items in a geodatabase). It looks like this in the expression of the query builder:

[Join_Count] in(SELECT TOP 9 [Join_Count] FROM spatial_join_geodat ORDER BY [Join_Count] DESC)

With this function I am able get a top n, but if the 9,10,11,12..... rows have the same value also these rows are selected. And sometimes the 9th row has a 0 as value and then all the rows are selected which I don't want. So, how do I limit the number of rows to 9 if this is the case?

Has anyone a solution for this?

Improve this question

1 Answer 1

Reset to default
1

this is just a workaround, but if you don't have a rationale for selecting within feature having the same number of joints, you could replace [join_count] with (n*[join_count]+[FID]). [FID] could of course be any other secondary sorting field, and the value of "n" should be larger than the number of feature in your geodatabase (e.g. n=100000). 

(100000*[join_count]+[OID]) in(SELECT TOP 9 (100000*[join_count]+[OID]) FROM spatial_join_geodat ORDER BY (100000*[join_count]+[OID]) DESC)
Improve this answer
3
  • I have tried to do it as you said, but then I get an empty output. This is what used: [Join_Count] in(SELECT TOP 9 (10000*[Join_Count]+ [OBJECTID_1]) FROM spatial_join_geodat ORDER BY [Join_Count] DESC) I don't exactly know what this workaround does and why I get an empty output?
    – rick
    Commented Jan 14, 2014 at 14:13
  • my suggestion was to build a new field with the same ranking information but with unique value. I am not expert in SQL so you can probably write it without actually creating the new field. But on your code you only replaced one occurrence of [joint_count] : it should also be done after ORDER.
    – radouxju
    Commented Jan 14, 2014 at 14:29
  • Thank you, it works now, I understand what the workaround does! So, I had to replace all [join_counts]!
    – rick
    Commented Jan 14, 2014 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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