[hypequery](https://hypequery.com/) › [Recipes](https://hypequery.com/recipes) › Top N per group in ClickHouse with LIMIT BY, compared with row_number() and rank() Index for agents: https://hypequery.com/llms.txt # Top N per group in ClickHouse with LIMIT BY, compared with row_number() and rank() (https://hypequery.com/recipes/top-n-per-group-limit-by) Get the top 3 paths for every tenant in one query with LIMIT 3 BY, typed in TypeScript, and see how ties change the answer under rank() and dense_rank(). ## Run it ```bash npx @hypequery/cli create top-n-per-group-limit-by -y cd top-n-per-group-limit-by pnpm install pnpm dev ``` - Packages: @hypequery/clickhouse, @hypequery/datasets (tested from hypequery 2.14.0) - Stack: node, clickhouse - Source: https://github.com/hypequery/hypequery/tree/main/recipes/top-n-per-group-limit-by - Test: `pnpm test` - Verify: `node dist/recipe.js --json` | Env var | Required | Description | | --- | --- | --- | | CLICKHOUSE_URL | no | ClickHouse HTTP endpoint (default http://localhost:8123) | | CLICKHOUSE_USERNAME | no | ClickHouse user (default: default) | | CLICKHOUSE_PASSWORD | no | ClickHouse password (default: hypequery, the seed's password) | | CLICKHOUSE_DATABASE | no | Database holding the seed tables (default hypequery_examples) | **The short answer:** `ORDER BY tenant_id, events DESC, path LIMIT 3 BY tenant_id`. `LIMIT n BY expr` keeps the first `n` rows for each distinct value of `expr`, in `ORDER BY` order, so the `ORDER BY` is your ranking. Add a tiebreak column to it, or which tied rows survive is not defined. In hypequery it is `.limitBy(3, 'tenant_id')` on the query builder. ## Demo ```text Top 3 paths per tenant, 2026-10-01 to 2026-10-31 tenant_id path events 1 /login 3 1 /explore 2 1 /export 2 2 /explore 53 2 /login 24 2 /reports 24 3 /explore 226 3 /dashboard 79 3 /billing 77 4 /explore 288 4 /settings 93 4 /reports 84 5 /explore 165 5 /dashboard 80 5 /login 75 ``` Tenant 1 has `/explore`, `/export` and `/reports` tied on 2 events. `LIMIT 3 BY` keeps exactly three rows, and the `path` tiebreak decides that `/reports` is the one left out. ## Install ```bash npx @hypequery/cli create top-n-per-group-limit-by -y cd top-n-per-group-limit-by docker compose -f seed/docker-compose.yml up -d --wait && (cd seed && pnpm install && pnpm seed) # or set CLICKHOUSE_* for your own ClickHouse pnpm install pnpm dev ``` ## What it shows - **LIMIT BY from the typed query builder** (`src/recipe.ts`): `.limitBy(3, 'tenant_id')` after the `orderBy` chain; column names and result types come from `src/schema.ts`. - **The same answer as `row_number()`** (`src/smoke.test.ts`): `row_number() OVER (PARTITION BY tenant_id ORDER BY events DESC, path) <= 3` returns identical rows. - **Ties, pinned**: `rank() <= 3` returns 4 rows for tenant 1, and `dense_rank() <= 3` returns all 10 of its paths plus 4 for tenant 2. - **One tenant is a plain LIMIT** (`src/dataset.ts`): a tenant-scoped dashboard does not need LIMIT BY; the dataset query with `limit: 3` renders `ORDER BY ... LIMIT 3`. - **The generated SQL is asserted** (`expected.sql`) and `output.json` must match exactly. ## Key file ```ts export function topPathsPerTenant() { return db .table('events') .select(['tenant_id', 'path']) .count('event_id', 'events') .where('tenant_id', 'in', TENANTS) .where('occurred_at', 'between', RANGE) .groupBy(['tenant_id', 'path']) .orderBy('tenant_id', 'ASC') .orderBy('events', 'DESC') .orderBy('path', 'ASC') .limitBy(TOP_N, 'tenant_id'); } ``` Renders: ```sql SELECT tenant_id, path, COUNT(event_id) AS events FROM events WHERE tenant_id IN (1, 2, 3, 4, 5) AND occurred_at BETWEEN '2026-10-01 00:00:00' AND '2026-10-31 23:59:59' GROUP BY tenant_id, path ORDER BY tenant_id ASC, events DESC, path ASC LIMIT 3 BY tenant_id ``` ## LIMIT BY or a window function? | You want | Use | | --- | --- | | Exactly N rows per group | `LIMIT N BY group`, or `row_number() ... <= N` | | Every row tied with the Nth (can be more than N) | `rank() ... <= N` | | The top N distinct values, with all their ties | `dense_rank() ... <= N` | | The rank number in the output | a window function; LIMIT BY does not expose a position | | Rows 2 and 3 of each group | `LIMIT 1, 2 BY group` (offset 1, then 2 rows) | On 25.8, `QUALIFY` filters on a window function without a subquery: `... QUALIFY row_number() OVER (PARTITION BY tenant_id ORDER BY events DESC, path) <= 3`. The test uses it as the ground truth. ## Pitfalls - **No tiebreak, no guarantee.** `ORDER BY tenant_id, events DESC LIMIT 3 BY tenant_id` is valid, but for tenant 1 any two of the three tied paths can come back. Add a unique column last in the `ORDER BY`. - **`LIMIT BY` then `LIMIT`.** `LIMIT 3 BY tenant_id LIMIT 5` returns 5 rows in total, not 5 per tenant: the plain `LIMIT` applies after `LIMIT BY`. The test pins it. - **`rank()` ignores your tiebreak** unless it is in the window's `ORDER BY`. With `ORDER BY events DESC` only, tied rows share a rank and all of them pass `<= 3`. - **Group first, then limit.** `LIMIT BY` runs after `GROUP BY` and `ORDER BY`, so it ranks the aggregated rows. To keep the latest 3 raw events per user, order by `occurred_at DESC` without a `GROUP BY`. ## Architecture ```text events ──GROUP BY tenant_id, path──▶ ORDER BY tenant, events DESC, path ──▶ LIMIT 3 BY tenant_id ──▶ 15 rows (the ranking) (the cut) src/recipe.ts topPathsPerTenant().toSQL() ──▶ expected.sql (asserted in CI) topPathsPerTenant().execute() ──▶ output.json (asserted exactly in CI) ``` ## Adapt it - **Top N per day.** `groupBy(['day', 'path'])` with `rawAs('toDate(occurred_at)', 'day')` in the select, and `.limitBy(5, 'day')`. - **Several group columns.** `.limitBy(3, ['tenant_id', 'event_type'])` keeps three rows per tenant and event type. - **Latest N events per user.** No aggregation: `.select(['user_id', 'event_type', 'occurred_at']).orderBy('user_id').orderBy('occurred_at', 'DESC').limitBy(10, 'user_id')`. - **Serve one tenant.** Use the dataset and its `limit`, scoped by `tenantKey`, behind `@hypequery/serve`; see the multi-tenant example. ## Related recipes - [p95-latency](https://hypequery.com/recipes/p95-latency): a dataset over the same events. ## Related examples - [multi-tenant-embedded-analytics](https://hypequery.com/examples/multi-tenant-embedded-analytics): tenant-scoped datasets behind an API. More on the clause itself: [ClickHouse LIMIT BY in TypeScript](https://hypequery.com/blog/clickhouse-limit-by-typescript). Tested on ClickHouse 25.8 with `@hypequery/clickhouse` 2.14 and `@hypequery/datasets` 0.29. ## Key file: src/recipe.ts ```typescript import fs from 'node:fs'; import path from 'node:path'; import { fileURLToPath } from 'node:url'; import { between, eq, type DatasetQueryFor } from '@hypequery/datasets'; import { analytics, db } from './client.js'; import { TenantPaths } from './dataset.js'; /** October 2026 for five tenants, fixed so the output is reproducible. */ export const RANGE: [string, string] = ['2026-10-01 00:00:00', '2026-10-31 23:59:59']; export const TENANTS = [1, 2, 3, 4, 5]; export const TOP_N = 3; /** * Top 3 paths per tenant in one query. LIMIT n BY col keeps the first n rows * of each col value in ORDER BY order, so the ORDER BY is the ranking: * tenant, then events descending, then path as a deterministic tiebreak. */ export function topPathsPerTenant() { return db .table('events') .select(['tenant_id', 'path']) .count('event_id', 'events') .where('tenant_id', 'in', TENANTS) .where('occurred_at', 'between', RANGE) .groupBy(['tenant_id', 'path']) .orderBy('tenant_id', 'ASC') .orderBy('events', 'DESC') .orderBy('path', 'ASC') .limitBy(TOP_N, 'tenant_id'); } /** One tenant only (a tenant-scoped dashboard): a dataset query with a plain LIMIT. */ export function topPathsForTenant(tenantId: number) { return { dimensions: ['path'], measures: ['events'], filters: [eq('tenantId', tenantId), between('occurredAt', RANGE[0], RANGE[1])], orderBy: [ { field: 'events', direction: 'desc' }, { field: 'path', direction: 'asc' }, ], limit: TOP_N, } satisfies DatasetQueryFor; } export function toSQL(): string { return topPathsPerTenant().toSQL(); } export async function run() { return topPathsPerTenant().execute(); } export async function runForTenant(tenantId: number) { return (await analytics.execute(TenantPaths, topPathsForTenant(tenantId))).data; } const root = path.resolve(path.dirname(fileURLToPath(import.meta.url)), '..'); const isMain = process.argv[1] && import.meta.url === new URL(`file://${process.argv[1]}`).href; if (isMain) { run() .then(async (rows) => { if (process.argv.includes('--write')) { // Capture the artifacts shown on the recipe page and asserted in CI. fs.writeFileSync(path.join(root, 'expected.sql'), `${toSQL()}\n`); fs.writeFileSync(path.join(root, 'output.json'), `${JSON.stringify(rows, null, 2)}\n`); console.log('wrote expected.sql and output.json'); } else if (process.argv.includes('--json')) { console.log(JSON.stringify(rows, null, 2)); } else { console.log(`Top ${TOP_N} paths per tenant, ${RANGE[0].slice(0, 10)} to ${RANGE[1].slice(0, 10)}\n`); console.table(rows); console.log('\nGenerated SQL:\n' + toSQL()); } await db.close(); }) .catch(async (error) => { console.error(error instanceof Error ? error.message : error); await db.close(); process.exit(1); }); } ``` ## Dataset: src/dataset.ts ```typescript import { dataset, dimension, measure } from '@hypequery/datasets'; /** * Events per path. The dataset owns the definition of the measure, and * covers the single-group case: top paths for one tenant is a plain * ORDER BY ... LIMIT, which a dataset query renders from `limit`. * * Top N for every tenant in one query needs ClickHouse's LIMIT BY, which the * query builder exposes as `.limitBy()` (see src/recipe.ts). Datasets do not * have LIMIT BY or window ranking today. */ export const TenantPaths = dataset('tenantPaths', { source: 'events', timeKey: 'occurred_at', dimensions: { eventId: dimension.number({ column: 'event_id' }), tenantId: dimension.number({ column: 'tenant_id' }), occurredAt: dimension.timestamp({ column: 'occurred_at' }), path: dimension.string({ label: 'Path' }), }, measures: { events: measure.count('eventId', { label: 'Events' }), }, }); ``` ## Generated SQL ```sql SELECT tenant_id, path, COUNT(event_id) AS events FROM events WHERE tenant_id IN (1, 2, 3, 4, 5) AND occurred_at BETWEEN '2026-10-01 00:00:00' AND '2026-10-31 23:59:59' GROUP BY tenant_id, path ORDER BY tenant_id ASC, events DESC, path ASC LIMIT 3 BY tenant_id ``` ## Output on the seed dataset ```json [ { "tenant_id": 1, "path": "/login", "events": "3" }, { "tenant_id": 1, "path": "/explore", "events": "2" }, { "tenant_id": 1, "path": "/export", "events": "2" }, { "tenant_id": 2, "path": "/explore", "events": "53" }, { "tenant_id": 2, "path": "/login", "events": "24" }, { "tenant_id": 2, "path": "/reports", "events": "24" }, { "tenant_id": 3, "path": "/explore", "events": "226" }, { "tenant_id": 3, "path": "/dashboard", "events": "79" }, { "tenant_id": 3, "path": "/billing", "events": "77" }, { "tenant_id": 4, "path": "/explore", "events": "288" }, { "tenant_id": 4, "path": "/settings", "events": "93" }, { "tenant_id": 4, "path": "/reports", "events": "84" }, { "tenant_id": 5, "path": "/explore", "events": "165" }, { "tenant_id": 5, "path": "/dashboard", "events": "80" }, { "tenant_id": 5, "path": "/login", "events": "75" } ] ``` ## Related - [Multi-tenant embedded analytics on ClickHouse with signed tokens](https://hypequery.com/examples/multi-tenant-embedded-analytics): Embed tenant-scoped ClickHouse analytics in your product: your server mints a short-lived token, hypequery enforces the tenant on every query. - [p95 latency per event type in ClickHouse with a typed dataset](https://hypequery.com/recipes/p95-latency): Define p50, p95 and p99 latency once as dataset measures and get the quantile() SQL, the result on a million events, and the exactness trade-off. - [All recipes](https://hypequery.com/recipes) - [/clickhouse-query-builder](https://hypequery.com/clickhouse-query-builder) - [/clickhouse-typescript](https://hypequery.com/clickhouse-typescript) - [/clickhouse-multi-tenant-analytics](https://hypequery.com/clickhouse-multi-tenant-analytics)