Table of Contents

Native extreme-row strategies (SelectWhereMax / SelectWhereMin)

For a SelectWhereMax/SelectWhereMin request with ExtremeRowTies.One, PostgreSQL and ClickHouse render a native SQL strategy instead of the portable window-function lowering — automatically, from the provider and the query form, with no opt-in and no per-instance switch.

Prerequisites: Keeping the extreme row · Provider overview · API quick reference

Operator names. The built-in operators are SelectWhereMinOld / SelectWhereMaxOld; they cover the compatibility path, including the in-memory provider. SelectWhereMin / SelectWhereMax from the extensions package are built on the public query-component layer.

Overview

SelectWhereMax and SelectWhereMin keep the row whose value under a selector is the greatest or smallest in the source, globally or per group. They are a row filter, not an aggregate: a surviving row keeps every column. The semantics are unchanged from the portable implementation — see Keeping the extreme row for the full contract.

What this page adds is how the One request reaches the database. Selection is automatic and deterministic by provider and query form/type:

  • on PostgreSQL, native-eligible key and group selectors render DISTINCT ON (grouped) or an inner ORDER BY ... LIMIT 1 (global);
  • on ClickHouse, a native-eligible key, group and payload render a single argMin/argMax aggregate;
  • every other combination keeps the portable window-function lowering.

There is no opt-in, no per-instance or per-query switch, and no retry-on-error: once the dialect accepts a request it renders it, and a rendering failure is surfaced instead of silently re-running the portable path. The hook covers ExtremeRowTies.One only: there is no native All renderer, so an All request takes the aggregate lowering described in Keeping the extreme row — GROUP BY plus a join-back per group, a scalar aggregate plus a join-back globally, component by component for a composite key. The one exception is a floating-point value key on ClickHouse, which keeps the ranking window: that server's max()/min() and its ORDER BY disagree about NaN (the aggregate returns NaN as the extremum while the ordering ranks it last), so the aggregate form would return a different winner set than the contract the window implements.

Usage

The public API does not change; only the provider decides the SQL.

// Global: the single row with the greatest Score.
var top = dataContext.From<Player>()
    .SelectWhereMax(p => p.Score)
    .ToList();

// Per group: the single row with the smallest Score in each TeamId.
// The group selector is compound; a bare p => p.TeamId would stay on the portable path.
var perTeam = dataContext.From<Player>()
    .SelectWhereMin(p => p.Score, ExtremeRowTies.One, p => new { p.TeamId })
    .ToList();

Native or portable

Provider Query form Strategy
PostgreSQL grouped One one DISTINCT ON (<group columns>) ... ORDER BY <group columns>, <extreme key> ASC/DESC
PostgreSQL global One an inner ORDER BY <extreme key> ASC/DESC LIMIT 1
ClickHouse global One one argMin/argMax(tuple(<payload columns>), <extreme key>), plus HAVING count() > 0 to suppress the empty-input default row
ClickHouse grouped One one argMin/argMax(tuple(<payload columns>), <extreme key>) next to the group columns and a GROUP BY
Any other provider, or a non-eligible One request any the portable window-function lowering
Every provider, ExtremeRowTies.All any the aggregate lowering: GROUP BY + join-back per group, a scalar aggregate + join-back globally, component by component for a composite key; a floating-point value key on ClickHouse keeps the ranking window instead (the server's aggregate and ordering disagree about NaN)

The PostgreSQL grouped form keeps the first row per group under the ordering, so ordering the group components first and the full extreme key after them selects the extreme row per group. The ClickHouse aggregate picks a whole payload tuple and projects its elements back to the canonical payload aliases. In both cases the shared builder still applies the outer projection, DISTINCT and the user's output ordering afterwards.

For the Player example above (players(id, score, team_id, name)) the two calls render as follows.

-- PostgreSQL, global (SelectWhereMax): the inner ORDER BY ... LIMIT 1
select t1.id, t1.score, t1.team_id as "TeamId", t1.name
 from (select *
 from players
 where score is not null order by "score" desc limit 1) as "t1"

-- PostgreSQL, grouped (SelectWhereMin over new { TeamId }): DISTINCT ON over the group columns
select t1.id, t1.score, t1.team_id as "TeamId", t1.name
 from (select distinct on ("team_id") *
 from (select *
 from players
 where score is not null) __nextorm_extreme order by "team_id", "score") as "t1"

-- ClickHouse, global (SelectWhereMax): one argMax aggregate over a payload tuple
select t1.id, t1.score, t1.team_id as `TeamId`, t1.name
 from (select tupleElement(`__nextorm_extreme_tuple`, 1) as `id`, tupleElement(`__nextorm_extreme_tuple`, 2) as `score`, tupleElement(`__nextorm_extreme_tuple`, 3) as `team_id`, tupleElement(`__nextorm_extreme_tuple`, 4) as `name`
 from (
 select argMax(tuple(`id`, `score`, `team_id`, `name`), `score`) as `__nextorm_extreme_tuple`
 from (select *
 from players
 where score is not null) as `__nextorm_extreme_src`
 having count() > 0)) as `t1`

-- ClickHouse, grouped (SelectWhereMin over new { TeamId }): the aggregate next to the group columns plus GROUP BY
select t1.id, t1.score, t1.team_id as `TeamId`, t1.name
 from (select `team_id`, tupleElement(`__nextorm_extreme_tuple`, 1) as `id`, tupleElement(`__nextorm_extreme_tuple`, 2) as `score`, tupleElement(`__nextorm_extreme_tuple`, 4) as `name`
 from (
 select `team_id`, argMin(tuple(`id`, `score`, `team_id`, `name`), `score`) as `__nextorm_extreme_tuple`
 from (select *
 from players
 where score is not null) as `__nextorm_extreme_src`
 group by `team_id`)) as `t1`

Eligible queries

Eligibility is intentionally narrow, and is decided from a prepared description of the command before any SQL, alias or parameter is built.

Part Native eligibility
Extreme key a direct mapped column bound to short/int/long or float/double (including nullable), or a composite of such components; a composite that contains a floating component is limited to three components, while a purely integral composite has no arity limit; a floating component is adapted NaN-safe
Group key the same integral types as the extreme key (short/int/long, including nullable), or a composite of those of any arity; a floating group key keeps the portable path
ClickHouse payload integral types (sbyte/byte/short/ushort/int/uint/long/ulong, including nullable), floating-point types (float/double, including nullable) and string
Everything else (an All request, whatever its keys) the portable lowering of that tie policy: the window for One, the aggregate form for All — except a floating-point value key on ClickHouse, which keeps the window

A composite key is compared lexicographically component by component. A Float32/Float64 component of the extreme key is adapted in the same query instead of being passed to argMin/argMax verbatim: a leading isNaN rank keeps NaN last in both directions, matching the portable contract, and a Float32 component is widened to Float64 for the comparison. The raw direct aggregate is deliberately not used, because ClickHouse seeds it with the first row and x > NaN/x < NaN are both false, so a leading NaN would otherwise win. The adaptation covers single, two- and three-component keys across Min/Max, global/grouped, and nullable/non-nullable components. The arity limits are deliberately asymmetric: a purely integral extreme key or group key is a plain lexicographic tuple and is not arity-capped, while only a key that contains a floating component is capped at three components, because only that adaptation was proven arity by arity. The group selector must be compound (e => new { e.TeamId }); a bare single-column group selector keeps the portable path. Everything outside the table above keeps the portable lowering with the same results contract: the other providers, every ExtremeRowTies.All request (which takes the aggregate form, since there is no native All renderer, except a floating-point value key on ClickHouse — that server's max()/min() treat NaN as the extreme value while its ORDER BY ranks it last, so the rank window stays the reference there), floating-point group keys, floating extreme keys with more than three components, Float16/Decimal keys, value converters, computed expressions, and other key or payload types.

Semantics

The native strategies do not change the observable result:

  • One real row per partition. One returns exactly one winning source row per group (one row for the global form), and every field comes from that same source row, so a full-entity read and a projection are consistent. Which tied row is chosen is unspecified — there is no tie-break API.
  • Output ordering is separate. A user OrderBy sorts the result only. It is applied outside winner selection and is never folded into the winner ordering.
  • NULL handling. A row with a NULL extreme-key component is excluded before winner selection, and a partition whose key components are all NULL yields no row. Nullable group keys and nullable payload values are preserved.
  • Empty input. The result is zero rows, including the ClickHouse global form, where HAVING count() > 0 suppresses the aggregate's default tuple row instead of returning it.
  • Rejected shapes. The combinations the portable path forbids — joins, paging, DistinctOn, GroupBy, Having, named windows, CTEs, set operations, LimitBy, ArrayJoin, PreWhere and non-physical sources — still reject before dispatch on both paths.

Dialect extensibility

The native strategy is a public dialect capability, not a hard-coded provider branch: ISqlDialect.ExtremeRowRenderer is an optional renderer hook (a default interface member returning null), and PostgresDialect/ClickHouseDialect are deliberately not sealed, so an external assembly can install its own public IExtremeRowRenderer. Returning null from the override — or a renderer whose CanRender answers false — keeps the portable window-function lowering.

To build such a dialect and register it on a context, see Extensibility: custom SQL dialects and renderers.

See also

Edit this page