Latest Contributions by JoshuaBixby

I am guessing one of Esri's responses, assuming they do respond, will be that uniqueness can be enforced through an Attribute Rule.  I find Attribute Rules clunky, but they are portable across a range of Esri products: var val = $feature.FieldName; var cnt = Count(Filter($featureset, 'FieldName = @VaL')); Boolean(cnt < 2); ... View more

This idea isn't really practical, and anything that looks like reordering in-place would actually be a multi-step recreation, renaming, and deletion bundled together, which wouldn't be any different than doing those steps manually oneself. In terms of SQL itself, there is no default order for records.  Looking to the section of the SQL standard that defines the behavior of cursors, ISO/IEC CD 9075-2 Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation), it clearly states (at least through SQL:99 that I have seen in person, but I assume the same language exists in later editions): When the ordering of a cursor is not defined by an <order by clause>, the relative position of two rows is implementation-dependent. The importance of that statement can't be emphasized enough.  The default ordering of records in a table is implementation dependent, and most vendors will state somewhere they don't guarantee a default ordering.  The Sort (Data Management)—ArcGIS Pro | Documentation gives a false-sense of assuredness to users because a table can be reconstructed a certain way, but Esri cannot guarantee the default ordering for every type of data store.  The main value, in my mind, of the Sort tool is to support spatial reordering of records so that indexing can be optimized to improve analysis performance in certain situations. Do records in a file geodatabase tend to have a predicable default ordering?  Yes, but it is an implementation artifact tied to ObjectID ordering and indexing.  I am not sure if Esri has ever documented a guaranteed default ordering, so if order matters an ORDER BY clause should always be used. ... View more

The major issue I see with "deploy it all with high availability" is licensing, all that functionality and uptime will come at a pretty steep cost, especially if it isn't needed to meet business needs. ... View more

I think this question raises even larger questions about Esri's plan for .NET LTS releases when MS only supports LTS editions for 36 months.  The issue of transitioning from .NET 6 to .NET 8 will be faced in another 36 months when they have to move from .NET 8 to .NET 10.  Esri has been averaging around 8-10 months between releases of ArcGIS Pro, so they likely won't be able to squeeze two more releases in before November, 2024. The stickier issue with MS's support timelines for .NET is that Esri's support lifecycle for ArcGIS Pro aren't lining up with MS's support lifecycle.  For example, ArcGIS Pro 3.2 was released on 11/07/2023 and is in "General Availability" support through 05/31/2025; however, .NET 6 support ends on 11/12/2024.  From an enterprise security perspective, that creates an issue because .NET 6 will need to be removed from machines around that date, which means ArcGIS Pro 3.2 won't work anymore. ... View more

Regarding not being able to create a database view with CAST in the WHERE clause, that is interesting because CAST works in the Select By Attributes tool, which of course is the WHERE clause:   CAST(t_date AS CHAR(20)) IS NOT NULL     ... View more

Rewriting the documentation and rewriting the SQL support are very different, and I suspect they only mean the former and not the latter.  That said, the documentation can definitely be polished up. ... View more

I wouldn't say it is logically impossible but technically impossible because of Esri's SQL implementation. Concatenating text with a date is possible, in certain places, with file geodatabases, but the date must be CAST to text before concatenating:  SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation CAST function The CAST() function converts a value or an expression from one data type to another specified data type. The syntax is as follows: CAST (expression AS data_type(length)) When casting a date to text in a file geodatabase, the outputted date format is: File geodatabases support the use of a time in the date field, so this can be added to the expression: Datefield = timestamp 'yyyy-mm-dd hh:mm:ss' So,  2/2/2021 3:00 PM casted to text is '2021-02-02 15:00:00'. Although dates can be cast to text and concatenated with additional text, e.g., a species name, it apparently isn't allowed within a subquery, so that takes it off the table in this case.  The following SQL works as a file geodatabase view   SELECT MAX(t_species || CAST(t_date AS CHAR(20))) FROM species_records GROUP BY t_species   but embedding that same SQL into a subquery (even a subquery in another file geodatabase view) generates an error.  Note:  The MAX function needs to be applied to the concatenated result and not just the date field. ... View more

It is supported in Postgres, it is called a row constructor on the left-hand side instead of row value, but the same syntax works for both: https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-IN row_constructor IN (subquery) The left-hand side of this form of IN is a row constructor, as described in Section 4.2.13     ... View more

For n=1, the results of your query and mine are the same.  That said, there are differences.  As I already mentioned, your query is only applicable for n=1 so it would not work in other n-cases.  Your query relies on a correlated subquery whereas mine relies on a couple co-routine calls and a couple subquery scans.  The correlated subquery is an expensive operation, but having more execution steps like in mine can add up as well.  How the two approaches perform relative to data set size?  I don't know.  Since SQLite supports a row value on the left-hand side of the IN operator, an alternative to n=1 that doesn't require a correlated subquery is: (t_species, t_date) IN ( SELECT t_species, max(t_date) FROM species_records GROUP BY t_species ) ... View more

The screenshot with the desired outcome appears to be greatest-n-per-group with "n" being 1 where the greatest has ties.  Are you interested in greatest-singular or greatest-n?  If the former, it simplifies the situation a bit. UPDATE:  The following is a generalized greatest-n-per-group that supports ties in SQLite.  I can't speak to its performance, and it is a bit ugly with the nested subquery (nesting is forced with a window function), but it works and is a place to start: ObjectID IN ( SELECT ObjectID FROM ( SELECT ObjectID, DENSE_RANK () OVER ( PARTITION BY t_species ORDER BY t_date DESC ) AS date_rank FROM speciesrecords ) WHERE date_rank <= 1 )     The date_rank controls the "n" being selected.   ... View more

I did a quick test using nested dicts instead of tuples in a dict, and it ran about 3x faster than my code.  But, then I refactored my tuple-in-dict code to cut out the max() function and use an if to compare the first value only, and it ran a bit faster than nested dicts.  I have some ideas why the max() function is noticeably slower, but regardless the cursor will be the slowest part of the code by an order of magnitude or more. ... View more

Are the values exactly the same between the dictionary key and the field in the table?  If there is extra text with the value in the field in the table, including any spaces, the match won't happen.  Also, does capitalization match?  It would help to provide sample data. ... View more

David, some Python-related thoughts on your code: Python defaultdict removes the need to check for a keys existence before comparing a key's value to a new value. Python min and max functions are purpose-built for comparing values and selecting the lowest or highest Tuple/list unpacking can be done in the for loop, a separate line isn't needed. Generator expressions were partially introduced to reduce the need to use filter() and map(), whose functional-programming style/nature somewhat contrasts with many other aspects of the Python language. import arcpy import datetime from collections import defaultdict # input feature class in_fc = r'C:\path to your FC' # default dictionary to store objectid of newest sighting date for each species date_dict = defaultdict(lambda: (datetime.datetime.min, -1)) # cursor to iterate through each row in fc # store the maximum (most recent) date for each species and its ObjectID with arcpy.da.SearchCursor(in_fc, ['OBJECTID', 'your species field', 'your date field']) as cursor: for oid, species, date in cursor: date_dict[species] = max(date_dict[species], (date, oid)) # create sql statement for selecting ObjectIDs sql = f"OBJECTID IN ({','.join(str(oid) for date,oid in date_dict.values())})" # turn into feature layer for selection feature_layer = arcpy.MakeFeatureLayer_management(in_fc, "feature_layer") # select by attributes using oid list/string selection = arcpy.management.SelectLayerByAttribute(feature_layer, "NEW_SELECTION", f'OBJECTID IN {selection_string}') # save your new fc out_fc = r'C:\path to new fc output' arcpy.CopyFeatures_management(selection, out_fc)   ... View more