MIN_BY

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the <value> from the row whose <key> is smallest among all rows in the aggregate (or within each group when used with GROUP BY).

1min_by(<value>, <key>)

• <value>: Any expression whose result is returned when its row wins the comparison. The aggregate result type matches this expression’s type.
• <key>: The expression MIN-BYuses to order rows. The type must support comparison.

The <value> from the row with the minimum <key> among considered rows.

Null handling:

• If the aggregate has no input rows (empty table or empty group), the result is NULL.
• If every row has a NULL key, those rows are skipped; if no row has a non-NULL key, the result is NULL.
• If the winning row’s <value> is NULL, the result is NULL (the key still determines which row wins).

Tie breaking:

When multiple rows share the same minimum key, one of the tied <value> results is returned; which tied row wins is implementation-dependent.

• Exactly two arguments are required (<value>, <key>). Calls with one argument or more than two arguments raise a syntax error.
• You can combine min_by with other aggregates, GROUP BY, HAVING, subqueries, and expressions in the arguments (for example, max_by(upper(name), score * 2)).
• min_by only supports a single value to sort on. To sort using multiple sort criteria, use FIRST_VALUE WITHIN GROUP.
• Rows with a NULL <key> are ignored when searching for the minimum key.

Returns the name and score from the row with the lowest score.

1SELECT min_by(name, score) RESPECT NULLS, min(score) AS low_scorer
2FROM (
3  VALUES
4    (1, 'Alice', 85),
5    (2, 'Bob', 92),
6    (3, 'Charlie', 78),
7    (4, 'David', 95),
8    (5, 'Eve', 88)
9) AS t(id, name, score);

Returns 'Charlie'.

With keys 5, NULL, 10, and 15, the minimum non-NULL key is 5, so the result is that row’s value.

1SELECT min_by(value, key_val) AS result
2FROM (
3  VALUES
4    (10, 5),
5    (20, NULL::int),
6    (30, 10),
7    (NULL::int, 15)
8) AS t(value, key_val);

Returns 10.

1SELECT region, min_by(product, revenue) AS worst_product
2FROM (
3  VALUES
4    ('North', 'A', 100),
5    ('North', 'B', 150),
6    ('South', 'A', 200),
7    ('South', 'B', 120)
8) AS sales(region, product, revenue)
9GROUP BY region;

Returns one row per region. For example North / 'A' and South / 'B'.

• MAX_BY
• MIN
• Aggregate Functions