Select Max value given two criteria

[jdejesus]

Hello,

I am looking to select a row based on the max number of two criteria. In the example below, i want to select the highest value ID, with the corresponding Currency and Acct Date. 

CURRENT

Currency	Acct Date	Net Local	Local Balance	ID
EURO	5/1/2019	-128.43	174239.7	1
EURO	5/10/2019	-103350.97	70888.73	1
EURO	5/15/2019	-70000	888.73	1
EURO	5/20/2019	93900.55	94789.28	1
EURO	5/21/2019	186823.21	281612.49	1
EURO	5/21/2019	-281000	612.49	2
U.S. DOLLARS	5/1/2019	237.35	433593.22	1
U.S. DOLLARS	5/2/2019	193500	627093.22	1
U.S. DOLLARS	5/10/2019	-627000	93.22	1
U.S. DOLLARS	5/13/2019	1000	1093.22	1
U.S. DOLLARS	5/13/2019	224060	225153.22	2
U.S. DOLLARS	5/15/2019	192800	417953.22	1
U.S. DOLLARS	5/22/2019	-417953.22	0	1
U.S. DOLLARS	5/24/2019	-212477.77	-212477.77	1

RESULT

Currency	Acct Date	Net Local	Local Balance	ID
EURO	5/1/2019	-128.43	174239.7	1
EURO	5/10/2019	-103350.97	70888.73	1
EURO	5/15/2019	-70000	888.73	1
EURO	5/20/2019	93900.55	94789.28	1
EURO	5/21/2019	-281000	612.49	2
U.S. DOLLARS	5/1/2019	237.35	433593.22	1
U.S. DOLLARS	5/2/2019	193500	627093.22	1
U.S. DOLLARS	5/10/2019	-627000	93.22	1
U.S. DOLLARS	5/13/2019	224060	225153.22	2
U.S. DOLLARS	5/15/2019	192800	417953.22	1
U.S. DOLLARS	5/22/2019	-417953.22	0	1
U.S. DOLLARS	5/24/2019	-212477.77	-212477.77	1

thank you.

[jasperlch · Accepted solution]

Hi @jdejesus 

This can be achieved by first adding a Sort tool by Currency Ascending, then Acct Date Ascending, then ID Descending, followed by a Unique tool where Currency and Acct Date are set as Unique Fields. 

Thanks,

Jasper

[ChrisTX · Accepted solution]

Another option is Summarize and Join

[jdejesus]

Thank you! i tested both and both work