SELECT

Query shape

WITH recent AS (
  SELECT market, price, quantity
  FROM trades
  WHERE observed_at >= now() - INTERVAL 5 MINUTE
)
SELECT
  market,
  count() AS trades,
  sum(quantity) AS volume,
  avg(price) AS average_price
FROM recent
GROUP BY market
HAVING volume > 0
ORDER BY volume DESC, market ASC
LIMIT 100;

The implemented clause order is:

[WITH ...]
SELECT [DISTINCT] projection
[FROM source]
[WHERE predicate]
[GROUP BY expression, ...]
[HAVING predicate]
[ORDER BY expression [ASC|DESC] [NULLS FIRST|LAST], ...]
[LIMIT count [OFFSET count]]

LIMIT and OFFSET accept integer literals or positional parameters.

Projection and expressions

Select columns, qualified wildcards, scalar expressions, functions, aliases, and lateral aliases:

SELECT
  t.*,
  price * quantity AS notional,
  notional / 100 AS notional_hundreds
FROM trades AS t;

Use cast(value, 'Type') when implicit conversion would be ambiguous. CASE, Boolean predicates, arithmetic, comparison, IN, BETWEEN, and null checks compose inside expressions.

SELECT id,
       CASE WHEN price >= 1000 THEN 'large' ELSE 'small' END AS band
FROM trades
WHERE market IN ('BTC-USD', 'ETH-USD')
  AND quantity IS NOT NULL;

Aggregation

Non-aggregate output expressions must be compatible with GROUP BY. HAVING can reference aggregate expressions and output aliases:

SELECT market, sum(quantity) AS volume
FROM trades
GROUP BY market
HAVING volume >= 100;

DISTINCT removes duplicate output rows. See Functions for the executable aggregate inventory.

CTEs and subqueries

NYXDB supports common table expressions, derived tables, scalar subqueries, and scalar WITH bindings:

WITH 1000 AS minimum_amount,
filtered AS (
  SELECT market, amount FROM trades WHERE amount >= minimum_amount
)
SELECT market, count()
FROM filtered
GROUP BY market;

Use aliases for derived tables and avoid relying on implicit name resolution in complex queries.

Set operations

UNION ALL concatenates inputs; UNION also removes duplicates:

SELECT market FROM spot_trades
UNION ALL
SELECT market FROM futures_trades;

Inputs must have compatible column counts and types.

Temporal and historical reads

Read reconstructed state at a supported system-time or valid-time point:

SELECT account_id, balance
FROM account_attributes FOR SYSTEM_TIME AS OF 42;

SELECT account_id, tier
FROM account_attributes FOR VALID_TIME AS OF 20;

In v1, HISTORICAL SELECT is restricted to an attribute table and one declared attribute, plus that table's entity and axis columns. For example, given a single-attribute table:

CREATE TABLE instrument_prices (
  instrument_id UInt64,
  observed_at DateTime64(6) NOT NULL,
  ATTRIBUTE (price Float64)
) SETTINGS kind = 'attribute',
           storage_policy = 'memory_data',
           entity = (instrument_id),
           valid_by = (observed_at);

HISTORICAL SELECT instrument_id, observed_at, price
FROM instrument_prices;

Selecting attributes from multiple independent attribute streams is rejected; use separate reads and reconcile their axes explicitly. Historical availability depends on retention and storage policy. Do not treat retained history as a replacement for backups.

Vector top-k

An indexed distance expression with ORDER BY and LIMIT can use HNSW:

SELECT id, cosine_distance(embedding, '[1,0,0,0]')
FROM items
ORDER BY cosine_distance(embedding, '[1,0,0,0]')
LIMIT 10;

See Vector search for current type, filtering, and fallback boundaries.

Explain a query

EXPLAIN returns the bound plan without running the read:

EXPLAIN SELECT market, sum(quantity)
FROM trades
GROUP BY market;

Always inspect representative production shapes and then validate observed latency and memory against real data distributions.

On this page