Selecting the most recent records based on unique values in another field

[SteveMasters]

Hi,

I have built the model in the picture, which works fine. But the next step I want to do is for each of the new feature classes that are created, I want only the most recent record of each unique record from the species field. But I want all the associated fields to come through into the new feature class. See table picture below, for example I only want the most recent record of Sparrowhawk. 

I am not sure how to do this and if I can add it into the model as part of the process?

Thanks

[Bud]

What version of Pro? Using your data or my fake data?

[SteveMasters]

3.2.1 and using my own data. Like I say the first SQL ran fine last week.

[Bud]

1. Does it work on my data? (you'd need to add a UNIQUE_ID column)
2. Is the date column in your data a proper date datatype? Can you send a screenshot of your table as well as a screenshot of the Fields View?
3. Using either my data or your data: What "query level" does it work at?
   For example, does it work if you just use the green SQL below, or green and blue, etc...
   1. Green only
   2. Green and blue
   3. Green, blue, and red
   4. All (green, blue, red, and black)
      

      select
          *
      from
          species_records s
      inner join
          (
          select
              min(objectid) as objectid,
              min(t_unique_id) as t_unique_id,
              t_species,
              max(t_date) as t_date
          from
              (select
                  *
              from
                  species_records
              inner join
                  (select
                      t_species,
                      max(t_date) as t_date 
                  from
                      species_records 
                  group by
                      t_species) l
                  on species_records.t_species = l.t_species 
                     and species_records.t_date = l.t_date
              )
          group by
              t_species
          ) l
          on s.t_unique_id = l.t_unique_id

4.  As a last resort, can you use the Summary Statistics tool on a database view that does work, such as using the last few steps in the Summary Statistics workflow I posted in a separate reply?
   https://community.esri.com/t5/arcgis-pro-questions/selecting-the-most-recent-records-based-on-unique... 

[RichardHowe]

First off you would need your "Date" field to be of type Date. Nothing programmatic is going to be able to analyse that currently with variants of free text, date formats and ranges. I would suggest using year (if the field was numeric) but I note some duplicates in that case. Once you have a nicely formatted date field then life would be simpler with python, but via model builder, off the top of my head...

Add a split by attributes, use "Species" field to split them and give you one feature class per species. Run a "Select by attributes" on each using:

Date = (SELECT MAX(Date) FROM <insertfeatureclassnamehere>)

Then merge each of the selections back into a single feature class

[DavidPike]

Probably the best model builder way as I think subqueries (in ArcGIS select by tool) are limited to scalar.

[SteveMasters]

Thanks for this @RichardHowe. I had a go with the model below, and ended up with the species all split out into feature classes, but then am unsure how to get them all back through the select by attribute process and then merge back. Do you have any other tips? Sorry I am new to model builder and python.

Also is there way for the process to cycle through the FGDB and do it for all? I could get a iterator to work and then input to the split by attributes.

[RichardHowe]

It's be a while since I used model builder tbf. If memory serves you can only have one iterator per model. So you would need to save that model and then embed it in a new one which will allow you to add another iterator which you could use with the "Make feature Layer" tool which you include the aofrementioned query with (giving you one feature per feature class), then use all those feature layers as inputs for the merge tool.

[SteveMasters]

I am struggling to get it work, I wonder if you might be able to demonstrate what you mean @RichardHowe?

I am happy for it to be python script if easier.

Thanks

[DavidPike]

Because the output feature classes from the split by attributes tool come from an iterator you can't just connect them to the merge tool (which I think you understand from your previous reply).

I think the recommended way is described here https://community.esri.com/t5/arcgis-pro-questions/merge-outputs-of-an-iterator-modelbuilder/td-p/11... by using the Collect Values tool https://pro.arcgis.com/en/pro-app/latest/tool-reference/modelbuilder-toolbox/collect-values.htm to collect the paths of the output feature classes.  You would then use the collected values as input to the merge.  Obviously before you do the merge you'd want to Select By Attributes on the MAX(Date) as Richard demonstrated.

[RichardHowe]

Ah yeah, good call. I had forgotten about the collect values option (as I said a while since I ever used MB). I think this should work fine.

If not though, my suggestion was to save what you have @SteveMasters and then create a second blank model. You will then be able to drag and drop your saved model into that blank model (just like any other tool) and then re-use iterator.