DISTINCT + DISTINCT ON

.distinct() dedups the result row set. .distinctOn([cols]) picks ONE row per unique value of the listed columns — the "latest per channel" / "best per user" pattern.

#.distinct() — basic dedup

await ctx.store.query(
  queryFor(database.messages)
    .select('userId')
    .distinct()
    .descriptor,
)
// → distinct user IDs that have any message

Cross-dialect: standard SQL, supported on every dialect we ship.

#.distinctOn([cols]) — pick the first per group

// Latest message per channel
await ctx.store.query(
  queryFor(database.messages)
    .distinctOn(['channelId'])
    .orderBy('channelId', 'asc')
    .orderBy('createdAt', 'desc')
    .descriptor,
)

The order matters: postgres picks the FIRST row per distinctOn-column combination AS DETERMINED BY THE FULL orderBy. Always set:

1. The distinctOn columns first in orderBy
2. The tie-breaker column second (which row to pick when multiple match — typically a timestamp or id)

Without that order, postgres still picks one row but the choice is unstable.

#Cross-dialect

• Postgres — native DISTINCT ON clause.
• MySQL / MariaDB / MSSQL / SQLite — no native DISTINCT ON. The framework falls back to plain DISTINCT and logs no warning because the fallback covers most use cases. If you specifically need one-row-per-group semantics on these dialects, use a window function with ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) and filter to rank = 1 in a sub-query.

#When NOT to use .distinct()

Distinct on a row set with multiple non-unique columns is rarely what you want — it dedups by the full row shape, including columns you might not have intended to constrain. If you want one row per some-key, use .distinctOn([key]) (postgres) or groupBy([key]) with aggregate({}) to be explicit.

#See also

• Aggregations — .groupBy() + window functions for cases distinct can't express
• Sub-queries — notInSubquery for "rows not appearing elsewhere"