0

I have feature class (Name: V1) in personal geodatabase with columns:

Height [float] and

Id [int] representing group number

I need to do SQL query that will select eight highest 'Height' values in every group. I know that syntax below is not working but this is what I want to accomplish: enter image description here

Reason I choose personal geodatabase instead of file one is that I can't create query below (I don't know why, because in personal geodatabase I can): enter image description here

  • I would use the Summary Statistics tool in an alternative workflow do this rather than SQL but it seems like you have made SQL a requirement. – PolyGeo Dec 16 '15 at 12:07
  • But how to prepare Summary Statistics to output N rows in a group? I thought that with Summary Statistics you can only obtain single result from a group (for example mean of 'Height' column for a group). – Roman Dec 16 '15 at 12:10
  • You can use more than one group field and that tool performs sorting. It's not a one step solution but I know I've done it that way in the past. There should be at least one earlier Q&A on the site that uses that technique. – PolyGeo Dec 16 '15 at 12:14
  • I can also split that feature class by group and for each output shapefile find top 8 rows. But I have to process lots of data that way... :/ – Roman Dec 16 '15 at 12:31
1

One of the possible solutions:

  1. Populate new TEXT field using

    "{:5d}{:8.3f}".format( !GROUP! , !HEIGHT! )

  2. Sort table into new feature class, using this field in ascending order

  3. Create new field, INCREM and populate it using OBJECTID or autoincrement. Unfortunately you cannot use OID field by some reason unknown to me

  4. Summary statistics using GROUP (case field), INCREM as statistics (MINIMUM)

  5. By populating yet another field (sorry) using INCREM-min.Increm find 1st eight values.

Much easier with python....

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.