Edit

Share via

arg_min() (aggregation function)

  • Article

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

Finds a row in the table that minimizes 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: argmin()

Syntax

arg_min (ExprToMinimize, * | ExprToReturn [, ...])

Learn more about syntax conventions.

Parameters

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

Null handling

When ExprToMinimize is null for all rows in a table, one row in the table is picked. Otherwise, rows where ExprToMinimize is null are ignored.

Returns

Returns a row in the table that minimizes ExprToMinimize, and the values of columns specified in ExprToReturn. Use or * to return the entire row.

Tip

To see the minimal value only, use the min() function.

Examples

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

StormEvents 
| summarize arg_min(BeginLat, BeginLocation) by State

The results table shown includes only the first 10 rows.

State BeginLat BeginLocation
AMERICAN SAMOA -14.3 PAGO PAGO
CALIFORNIA 32.5709 NESTOR
MINNESOTA 43.5 BIGELOW
WASHINGTON 45.58 WASHOUGAL
GEORGIA 30.67 FARGO
ILLINOIS 37 CAIRO
FLORIDA 24.6611 SUGARLOAF KEY
KENTUCKY 36.5 HAZEL
TEXAS 25.92 BROWNSVILLE
OHIO 38.42 SOUTH PT
... ... ...

Find the first time an event with a direct death happened in each state, showing all of 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 lowest value for StartTime.

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

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

State StartTime EndTime ...
INDIANA 2007-01-01T00:00:00Z 2007-01-22T18:49:00Z ...
FLORIDA 2007-01-03T10:55:00Z 2007-01-03T10:55:00Z ...
NEVADA 2007-01-04T09:00:00Z 2007-01-05T14:00:00Z ...
LOUISIANA 2007-01-04T15:45:00Z 2007-01-04T15:52:00Z ...
WASHINGTON 2007-01-09T17:00:00Z 2007-01-09T18:00:00Z ...
CALIFORNIA 2007-01-11T22:00:00Z 2007-01-24T10:00:00Z ...
OKLAHOMA 2007-01-12T00:00:00Z 2007-01-18T23:59:00Z ...
MISSOURI 2007-01-13T03:00:00Z 2007-01-13T08:30:00Z ...
TEXAS 2007-01-13T10:30:00Z 2007-01-13T14:30:00Z ...
ARKANSAS 2007-01-14T03:00:00Z 2007-01-14T03: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_min(Version, *) by Fruit

Output

Fruit Version Color
Apple 1 Red
Banana Yellow
Pear 1 Brown

Comparison to min()

The arg_min() function differs from the min() function. The arg_min() function allows you to return additional columns along with the minimum value, and min() only returns the minimum value itself.

Examples

arg_min()

Find the first 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 lowest value for StartTime.

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

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

StartTime EndTime EpisodeId EventId State EventType ...
2007-01-01T00:00:00Z 2007-01-22T18:49:00Z 2408 11929 INDIANA Flood ...

min()

Find the first 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 minimum value for StartTime.

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

The results table returns the lowest value in the specific column only.

min_StartTime
2007-01-01T00:00:00Z

Related content