Edit

Share via

arg_max() (aggregation function)

  • Article

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Finds a row in the table that maximizes the specified expression. It returns all columns of the input table or specified columns.

Note

This function is used in conjunction with the summarize operator.

Deprecated aliases: argmax()

Syntax

arg_max (ExprToMaximize, * | ExprToReturn [, ...])

Learn more about syntax conventions.

Parameters

Name Type Required Description
ExprToMaximize string ✔️ The expression for which the maximum value is determined.
ExprToReturn string ✔️ The expression determines which columns' values are returned, from the row that has the maximum value for ExprToMaximize. Use a wildcard * to return all columns.

Returns

Returns a row in the table that maximizes the specified expression ExprToMaximize, and the values of columns specified in ExprToReturn.

Tip

To see the maximal value only, use the max() function.

Examples

Find the maximum latitude of a storm event in each state.

StormEvents 
| summarize arg_max(BeginLat, BeginLocation) by State

The results table displays only the first 10 rows.

State BeginLat BeginLocation
MISSISSIPPI 34.97 BARTON
VERMONT 45 NORTH TROY
AMERICAN SAMOA -14.2 OFU
HAWAII 22.2113 PRINCEVILLE
MINNESOTA 49.35 ARNESEN
RHODE ISLAND 42 WOONSOCKET
INDIANA 41.73 FREMONT
WEST VIRGINIA 40.62 CHESTER
SOUTH CAROLINA 35.18 LANDRUM
TEXAS 36.4607 DARROUZETT
... ... ...

Find the last time an event with a direct death happened in each state, showing all the columns.

The query first filters the events to only include those where there was at least one direct death. Then the query returns the entire row with the most recent StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_max(StartTime, *) by State

The results table displays only the first 10 rows and first 3 columns.

State StartTime EndTime ...
GUAM 2007-01-27T11:15:00Z 2007-01-27T11:30:00Z ...
MASSACHUSETTS 2007-02-03T22:00:00Z 2007-02-04T10:00:00Z ...
AMERICAN SAMOA 2007-02-17T13:00:00Z 2007-02-18T11:00:00Z ...
IDAHO 2007-02-17T13:00:00Z 2007-02-17T15:00:00Z ...
DELAWARE 2007-02-25T13:00:00Z 2007-02-26T01:00:00Z ...
WYOMING 2007-03-10T17:00:00Z 2007-03-10T17:00:00Z ...
NEW MEXICO 2007-03-23T18:42:00Z 2007-03-23T19:06:00Z ...
INDIANA 2007-05-15T14:14:00Z 2007-05-15T14:14:00Z ...
MONTANA 2007-05-18T14:20:00Z 2007-05-18T14:20:00Z ...
LAKE MICHIGAN 2007-06-07T13:00:00Z 2007-06-07T13:00:00Z ...
... ... ... ...

The following example demonstrates null handling.

datatable(Fruit: string, Color: string, Version: int) [
    "Apple", "Red", 1,
    "Apple", "Green", int(null),
    "Banana", "Yellow", int(null),
    "Banana", "Green", int(null),
    "Pear", "Brown", 1,
    "Pear", "Green", 2,
]
| summarize arg_max(Version, *) by Fruit

Output

Fruit Version Color
Apple 1 Red
Banana Yellow
Pear 2 Green

Comparison to max()

The arg_max() function differs from the max() function. The arg_max() function allows you to return additional columns along with the maximum value, and max() only returns the maximum value itself.

Examples

arg_max()

Find the last time an event with a direct death happened, showing all the columns in the table.

The query first filters the events to only include those where there was at least one direct death. Then the query returns the entire row with the most recent (maximum) StartTime.

StormEvents
| where DeathsDirect > 0
| summarize arg_max(StartTime, *)

The results table returns all the columns for the row containing the highest value in the expression specified.

StartTime EndTime EpisodeId EventId State EventType ...
2007-12-31T15:00:00Z 2007-12-31T15:00:00 12688 69700 UTAH Avalanche ...

max()

Find the last time an event with a direct death happened.

The query filters events to only include those where there is at least one direct death, and then returns the maximum value for StartTime.

StormEvents
| where DeathsDirect > 0
| summarize max(StartTime)

The results table returns the maximum value of StartTime, without returning other columns for this record.

max_StartTime
2007-12-31T15:00:00Z

Related content