Sort and top-k

sort(by) returns a new table with the rows in order. top(k, by) returns only the first k rows of that order, without sorting the rest. Both take the same by.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  price:  [120, 80, 45, 300, 99, 15],
  qty:    [2, 1, 4, 1, 3, 10],
});

console.log(orders.sort('-price').toRows(3));        // largest price first
console.log(orders.top(2, '-(price * qty)').toRows()); // two biggest orders by value
await engine.close();

Sort keys

by	Meaning
'price'	smallest first
'-price'	largest first
'-(price * qty)'	any expression, largest first
['region', '-price']	by region, then by price within a region, largest first
'region'	text sorts by label, A to Z ('-region': Z to A)

The rules that decide ties and gaps:

• Stable. Rows with equal keys keep their table order.
• Missing values last. NaN sorts after every number, whichever direction you sort in.
• Filters apply first. orders.filter("region == 'EU'").sort('-qty') sorts only the EU rows.

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  price:  [120, 80, 120, 300, NaN, 15],
  qty:    [2, 1, 4, 1, 3, 10],
});

console.log(t.sort(['region', '-price']).toRows());
console.log(t.sort('price').toRows());  // the NaN row comes last
await engine.close();

How sorting runs

A sort of 50,000 rows or more runs on every core. The key columns go through a parallel radix sort (11-bit digits; digits that never change are skipped, whole numbers sort as offsets), then every column is gathered in the new order on every core. Text keys are sorted through the rank of each label, so a text sort costs the same as a number sort.

Sorting 10M rows by -price takes about 130 ms. On one thread it takes about 350 ms, and a JavaScript comparator sort (rows.sort((a, b) => b.price - a.price)) about 4.6 s.

top: the first k without a full sort

Most “sort” questions are really “top 10” questions: biggest customers, slowest requests, latest events. top(k, by) answers them without ordering every row. It takes a sample, estimates a cutoff for the first key, then counts and collects only the rows past that cutoff on all cores, and sorts just those. If the cutoff turns out too tight it widens it, so the answer is the same as sort(by) followed by taking k rows, ties included. On tables of 50,000 rows or fewer, when k is more than an eighth of the rows, or when the first key is text, top simply sorts.

import { Engine } from 'vectrojs';

const engine = new Engine();
const n = 1_000_000;
const t = engine.table({
  id: Float64Array.from({ length: n }, (_, i) => i),
  latency: Float64Array.from({ length: n }, (_, i) => (i * 7919) % 100_003),
});

let start = performance.now();
const slowest = t.top(5, ['-latency', 'id']);
console.log('top', (performance.now() - start).toFixed(1), 'ms', slowest.toRows());

start = performance.now();
const sorted = t.sort(['-latency', 'id']);
console.log('sort', (performance.now() - start).toFixed(1), 'ms', sorted.toRows(5));
await engine.close();

topAsync and sortAsync do the same without blocking. On split tables and streams, topAsync is available but there is no full sort yet.

head, slice and toRows(limit)

These take rows by position, after any filter.

Method	Returns
t.head(n)	a new table with the first n rows
t.slice(start, end)	a new table with rows start up to (not including) end; end defaults to the last row, and both are clamped to the table
t.toRows(limit)	the first limit rows as plain objects

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({ day: [1, 2, 3, 4, 5, 6], visits: [30, 41, 25, 60, 52, 47] });

console.log(t.head(2).toRows());
console.log(t.slice(2, 4).toRows());
console.log(t.filter('visits > 40').toRows(2));

// page 2 of the busiest days, 2 per page
console.log(t.sort('-visits').slice(2, 4).toRows());
await engine.close();

head, slice and sort each build a new table. When you only need a few rows to show, top or a lazy limit does less work.

limit and top in lazy queries

A lazy query has sort(by), limit(n, offset) and top(k, by). The planner turns sort followed by limit into a top-k, so writing them as two steps costs nothing extra. Over 10M orders, “sort then take the first 10” went from 177 ms step by step to 12 ms as a lazy query.

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({
  cust:  [1, 2, 3, 4, 5, 6],
  spend: [310, 120, 980, 45, 640, 220],
});

const q = t.lazy().sort('-spend').limit(2, 1); // skip the first, take two
console.log(q.explain());
console.log(q.toRows());
await engine.close();

The plan reads top 3 by -spend (sort + limit), then skip 1.

In SQL

ORDER BY ... ASC | DESC [NULLS FIRST | NULLS LAST], LIMIT n, OFFSET m and FETCH FIRST n ROWS ONLY compile to the same steps, so ORDER BY ... LIMIT is a top-k too. NULLS FIRST is the one ordering the table methods don’t offer: it puts missing values at the start. See SQL.