I have a large feature class table with wind measurements from weather stations. It has the following fields:

  • Station ID field (repeated hundreds of times)
  • Measurement result
  • Date of the measurement

I'd like to select the highest measurement result from each of the stations and preserve both the measurement result and the date fields.

Using Dissolve tool (with the statistic field set to max for the measurement field) means I lose the date field. A later join will not give the exact date for the highest measurement.

I've considered Split features by station ID to iterate selecting the highest value (with the aim of merging them with only the selected max values), but cannot find a SQL expression that returns the max measurement value. I've tried

    [MeasurementField] in (SELECT max([MeasurementField]) FROM WindMeasurementTable)

But with no success.

I am using both ArcGIS Desktop 10.3 and Pro.

  • 1
    Sort descending (wind speed), delete identical (station id). – FelixIP Mar 27 '19 at 19:46
  • This is a good option for a smaller table, I'll have hundreds of tables with the same fields stretching back almost a century. Is there a way to sort in model builder or scripting? – Sil Mar 29 '19 at 8:52
  • As @felixip suggested but using a DA cursor, you can sort multiple columns Asc or Desc and set an attribute for the max record. This way you are not duplicating your data. – klewis Mar 29 '19 at 16:40
  • Unfortunately the sorting of columns + deleting identical doesn't seem to automatically keep the top record and delete the rest. I've tried this method several times and the records that are deleted are chosen randomly it seems. But thanks to the both of you for your help, I do appreciate the feedback. – Sil Apr 8 '19 at 14:33

Your Answer

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

Browse other questions tagged or ask your own question.