One-to-first join — Control what related record gets used

[Bud]

It would be helpful if we could control the logic used for getting the records on the many side of [one-to-first] joins.

For example, choose an ORDER BY field: Grab the related record with the latest date, or the largest integer, etc. Specify ascending or descending order.

Even better, make it a user-defined ORDER BY sql clause, so that we can use specialized logic like:

• SQL CASE statement
• Multiple sorting fields, some ASC and some DESC
• NULLS FIRST or NULLS LAST (relatively unknown SQL functionality)
  • Specify how nulls are sorted in the attribute table (nulls first or last)

Related: Choose if join will be 1:1 or 1:Many

[Bud]

1:M Join to Pull feature with Specific Attribute information

Sort within groupings, join, and fill gaps using a single tool

[Bud]

Possible workarounds:

Since one-to-first joins use the related record that has the lowest OBJECTID, we could consider sorting the rows for each grouping/common ID, along with generating a new OBJECTID on the sorted data, using techniques like:

1. Query layer (enterprise geodatabases only) (untested)
   

   select
       cast(rownum as int) as objectid, --Fake OBJECTID. Uses sorted subquery.
       a1,
       b1
   from
       (select
           a1,
           b1
       from
           table_1
       order by
           a1,
           b1 desc)

2. Database view (mobile or enterprise geodatabase; file geodatabase SQL is likely too limited) (untested)
3. Sort geoprocessing tool (static output)
4. Other? Python?

ROW_NUMBER function in FGDB SQL
https://community.esri.com/t5/arcgis-pro-ideas/row-number-function-in-fgdb-sql/idc-p/1526715/highlig... 

[Bud]

Related:

• An idea that's similar, but it's for spatial joins, not tabular joins: Spatial Join — "One-to-one (ranked by field)" join operation
• Idea: Consistent one-to-one/one-to-first wording in GP tools