MAX Date GROUP BY id in Model Builder

JustinWolff — Wed, 09 Dec 2020 20:51:26 GMT

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:

Source countySL table

And if I run the query below I get what I need:

functional query in SSMS

But in ArcGIS Pro model builder, if I use the query below:

SQL in Model Builder

I get this:

Table Select results

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

Re: MAX Date GROUP BY id in Model Builder

KevinMacLeodCAI — Fri, 11 Jun 2021 13:46:00 GMT

I see the same results. Works fine for other numeric fields though. Doesn't work for date. Same as you, I'm seeing the duplicates as if it didn't Group on the ID field and then grab the most recent date. SQL Server 2017, ArcGIS SDE 10.7.1 and then trying this syntax straight from the Help on an ArcMap 10.7.1 definition query.

Workaround - I resorted to making it a view in SSMS as a solution and it works beautifully. For my purposes I just needed it as a layer to have on a service. So I then added the view to ArcMap. I deduce there is some limitation or bug in Esri's SQL implementation with dates? Maybe it will get implemented in a future update? That is all I can deduce, since it works great in native SQL as Justin nicely outlined.

topic Re: MAX Date GROUP BY id in Model Builder in Geodatabase Questions

MAX Date GROUP BY id in Model Builder

Re: MAX Date GROUP BY id in Model Builder