MAX Date GROUP BY id in Model Builder
Version: ArcGIS Pro 2.6.3
Data: Enterprise SQL 2016
I have a table of parcel sales data, with ParcelID field (text type) as the parcel identifier and SaleDate (date type). There are multiple rows for the same ParcelID value, each with a different SaleDate value. I want a selectio of the records keeping only the latest date, within model builder (it's part of a larger process).
Based on the guidance here: https://support.esri.com/en/technical-article/000008936 I have the following in a Select Layer By Attribute or Table Select tool - both have the same results:
SaleDate IN (SELECT MAX(SaleDate) FROM countySL GROUP BY ParcelID)
I cannot get this to return the correct values.
I run this in SSMS and get exactly what I'd expect:
SELECT n.*
FROM [dbo].[COUNTYSL] n
INNER JOIN (
SELECT ParcelID, MAX(SaleDate) AS SaleDate
FROM [dbo].[COUNTYSL] GROUP BY ParcelID
) AS MAX ON MAX.ParcelID = n.ParcelID AND MAX.SaleDate = n.SaleDate ORDER BY ParcelID;
GO
I had originally built everything around FGDB, but moved to Enterprise SQL to use GROUP BY functionality. Is there something special with the date format?
In SSMS, this is what the countySL data looks like:
And if I run the query below I get what I need:
But in ArcGIS Pro model builder, if I use the query below:
I get this:
I've attempted using the Select Layer By Attribute tool as well, and messed with the 'Invert Where Clause' and attempted to switch selection from the source table, but it doesn't work either. What am I missing?
Thanks
