1

I have a personal GDB table that has a non-unique SLID field and a date field. Basically there are multiple records with the same SLID numbers but different dates. I want to select only the most recent records for each SLID number. I have tried this query statement in the Select by Attributes window:

    [Date_] IN (SELECT MAX([Date_]) FROM table_name GROUP BY [SLID])

This hasn't worked, because for some of the SLIDs, it selects multiple instances of the same SLID value, even if the dates are different. For instance, for SLID 27, the query selects both the instances from 5/28/2013 and from 12/12/2008. What am I doing wrong?

The SLID field is short integer format, and the Date_ field is date format.

  • Some thing to double check, are your SLID values actually different? For example "SLID 27", "SLID27" and " SLID 27" are all different but a cursory glance will miss this. – Hornbydd Jun 16 '15 at 20:55
  • I would use Summary Statistics to find the Max Date_ and case of SLID.. yes, it's in another table but you've got your answer there... I'm not convinced that SQL from ArcGis works as it should for pGDB. If you have MS Access (or equivalent) you can run the query directly on the data. To get a selection join by date and then select SLID = SUM_TABLE.SLID – Michael Stimson Jun 16 '15 at 21:44
  • Thank you for the suggestions. I've scoured the fields many times for incorrect formatting, so I don't think that is the issue. I did try to do it in Access and I was successful, but I need to be able to do everything within ArcMap with a python script. I will try the Summary Statistics option and report back for others who may be having this issue. – merbes00 Jun 17 '15 at 21:58
  • The Summary Statistics function was the way to go. I will edit the original post with the solution. – merbes00 Jun 18 '15 at 15:43
  • @MichaelMiles-Stimson Perhaps you or the asker can cut/paste the answer from within the question out into an answer so that this conforms to our Q&A format? – PolyGeo Jun 20 '15 at 4:56
2

Using the Summary Statistics option on the SLID field with "High" values for the Date_ field gave me the most recent records. You can then create concatenated fields on which to base a join, and join the original table to the summary statistics table, thus bringing in all the other relevant fields for the records.

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.