Using a single tool: Sort within groupings, join, and fill gaps

[Bud]

@JustinH mentions a challenge in this post: 1:M Join to pull feature with specific attribute information. My understanding is that the requirements are:

“Using an AGOL Feature Service that our organization is hosting...”

1. Using the join table, sort within the A1 groupings so that the row where B1=1 is at the top of each grouping.
2. Perform a join using the input table and the join table, creating a one-to-first join. The one-to-first join will automatically use the related row with the lowest OBJECTID, that explains why we did the sort in the step above.
3. Where there is an input table row but no matching join table row, replace the nulls with zeros in B1.

That'd be easy in a database view when using a geodatabase that has fully functioning SQL, such as a mobile or enterprise geodatabase:

--Oracle SQL - database view (or query layer)
select
    t2.a2,
    t1.a1,
    case when t1.b1 is not null then t1.b1 else 0 end as b1
from
    table_2 t2
left join
    (select a1, b1, row_number() over (partition by a1 order by b1 desc) as rn from table_1) t1
    on t2.a2=t1.a1
where
    (t1.rn = 1 or t1.rn is null)

But it'd take a few different steps and multiple static outputs to do it with AGOL or FGDB data. It seems like it's more awkward than it needs to be, so I'm wondering if Pro could be enhanced so that the workflow could be done using a single tool (or two tools at the most).

Edit - I suppose this could be considered a partial duplicate of this idea: Join — Control what related record gets used

[JonathanNeal]

@Bud For this one we attempted to sort by a field (other than ObjectID) in the 3.3 release with our one-to-first parameter to the Add Join Tool, but there is further work to be done to complete this for fields.  I'll relate this idea to the previous work for our tracking.  (Hopefully we can get some Kudo's on this one to help us sell it to the other team).