I'm trying to select records using "Select Layer by Attribute" using a subquery. My input to the query is a layer (review_records) in the project that has a definition query applied (field "Assessment" is null); the source is a File Geodatabase feature class. I can do simple queries using the edit clause mode without any problems. However, when I try and use SQL with a subquery I cannot reference the layer name, only the underlying feature class name. This statement:
OBJECTID = (SELECT MIN(OBJECTID) FROM review_records)
won't validate. The same statement referencing the feature class name validates fine:
OBJECTID = (SELECT MIN(OBJECTID) FROM ben_fp)
but it does not return any selected records as the minimum OBJECTID value does not meet the condition set in the definition query. If I change MIN to MAX one record is selected as I would expect (the max objectid record has NULL for the assessment field).
I feel like I'm missing something obvious--is there a better way to do this (other than creating a new feature class that only has NULL assessment values)?