How do I find duplicates within a file geodatabase, and query within the groups of duplicate records?

Question

Im currently attempting to query a file geodatabase, where I want to find all the duplicate PID (sub for an OBJECTID), and then within each one of these matching groups of records, find where the LAND_SIZE_ACRES has increased over time (each row of the same PID represents land data of sequential years).

I have been using "Select By Attributes," where I've used this formula, except it doesnt seem to work on a file geodatabase:

[FIELD_NAME] In (SELECT [FIELD_NAME] FROM [TABLE_NAME] GROUP BY [FIELD_NAME] HAVING Count(*)>1 )

Therefore I need to be able to query each group of matching records, and then find the variation within said groups of records. I just dont know where to start.

Example data:

Very few of the records actually change in land_size_acres overtime, hence the picture with no variation.

Answer 1

I could be wrong but I dont think your query is possible when data is in a file geodatabase, see SQL reference for query expressions used in ArcGIS the Subqueries part:

File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support

You can use python though:

import arcpy
from collections import defaultdict

layer = 'new_layer' #Change to match your data. If outside ArcMap, create the layer using MakeFeatureLayer
fields = ['PID','SIZE'] #Change


d = defaultdict(list)
with arcpy.da.SearchCursor(layer,fields) as cursor:
    for pid,size in cursor:
        d[pid].append(size)

#d is now a dictionary with pid as key, and corresponding sizes as values:
#defaultdict(<class 'list'>, {1: [1.0, 1.0, 1.1, 1.1], 2: [3.0, 3.0, 3.0], 3: [4.0, 3.0, 5.0]})

increasing_pids = []
for key, values in d.items(): #d.iteritems() in python2/ArcMap
    if len(set(values))>1: #If all sizes are the same, length of the set will be 1
        if any(i<j for i,j in zip(values, values[1:])): #If size is ever increasing
            increasing_pids.append(key) #then store PID in list

if len(increasing_pids)==1: #If only one item in list, append one dummy value or tuple below will cause syntax error, for example look like: (123,)
    increasing_pids.append(-999)

sql = "{0} IN{1}".format(arcpy.AddFieldDelimiters(datasource=layer, field=fields[0]), tuple(increasing_pids))
arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer,
                                        where_clause=sql)