SearchKit: how to find largest contributions?

Question

Suppose I want to display a table of largest contribution per contact AND its date.

Consider:

This search produces the contact name, a list of amounts and a list of dates.

Changing the field transformation function of Total Amount from 'List' to 'Max' produces the largest amount. The problem is in getting the date associated with that largest amount.

SK ends up producing an SQL statement, and looking at the problem from a SQL perspective, there is a section of the MySQL manual addressing this. So there are various ways in can be achieved in SQL, but can SK produce such SQL?

Please note: This largest contribution question is just an example of the more general question of selecting data associated with a max/min field with SK, so I'm not looking for solutions involving things like Summary Fields (useful as it is!).

Answer 1

18 months later and SK can now do this with the 'First' transformation:

Answer 2

I played around with this a bit and the closest I got was to create a table of top donations not grouped by contact. I know it's not what you asked for, but it could be serviceable. If I were a fundraising officer I'd probably want to know that the top 1, 2, and 3 donations were actually all from the same person rather than have that fact hidden by the GROUP BY which would make it look like the 4th top donation was really the 2nd.

To get exactly what you asked for, we'd probably have to implement the sometimes requested feature of creating a search from the results of another search.