# SELECT

> One-shot SELECT as the engine parses it - projections, WHERE, GROUP BY and aggregates, HAVING, ORDER BY, LIMIT and OFFSET, AS OF COMMIT and set operations.

A one-shot `SELECT` reads the current state of one or more relations (joined;
see [Joins](/docs/sql/joins)) and returns a finite result. For a query that
keeps running and delivers changes, prefix it with `STREAM`; see
[Streaming queries](/docs/sql/streaming).

## Clause order

The clauses are parsed in this order; each is optional except `FROM`:

| Clause | Form |
| --- | --- |
| projection | `SELECT [DISTINCT] *` or `SELECT <output> [AS <name>], …` |
| source | `FROM <relation> [AS <alias>]` or `FROM <relation> AS OF COMMIT <n>` |
| filter | `WHERE <condition>` |
| grouping | `GROUP BY <column or output>, …` |
| group filter | `HAVING <aggregate output> <comparison> <literal>` |
| ordering | `ORDER BY <output> [ASC or DESC] [NULLS FIRST or NULLS LAST], …` |
| snapshot | `AS OF COMMIT <n>`, when not written after the relation |
| paging | `LIMIT <n>` then `OFFSET <n>` |
| settings | `SETTINGS …`, the final clause; see [SETTINGS](/docs/sql/settings) |

A relation is a table, a view, or a system relation such as `system.tables`
([System tables](/docs/operations/system-tables)).

## Projections and aggregates

```sql
SELECT *
FROM balances;
```

```sql
SELECT address, token, amount
FROM balances
WHERE chain_id = 1
LIMIT 10;
```

```sql
SELECT COUNT(*) AS transfers, SUM(amount) AS volume
FROM transfers
WHERE token = 'USDC';
```

An output is a column, an expression or function call (see
[Functions](/docs/functions)), or an aggregate such as `COUNT`, `SUM`, `MIN`,
`MAX`, `AVG` and `COUNT(DISTINCT …)`. `COUNT()` and `COUNT(*)` count rows.
Without `AS`, an output is named after its column; an aggregate is named after
the function and its argument, such as `count` or `sum_amount`; another
expression is named `expression_<position>`.

## WHERE

A condition combines predicates with `AND`, `OR`, `NOT` and parentheses. A
predicate is one of:

- `<column> =`, `<>`, `!=`, `<`, `<=`, `>` or `>=` a literal, a parameter or
  another column;
- `<column> [NOT] IN (<literal>, …)`;
- `<column> [NOT] BETWEEN <literal> AND <literal>`;
- `<column> [NOT] LIKE '<pattern>'`;
- `<column> IS [NOT] NULL`;
- `(<column>, …) IN ((<literal>, …), …)`, a row-value list.

A comparison with NULL is not true, so `WHERE` keeps only rows whose condition
is true. Use `IS NULL` to match nulls.

```sql
SELECT address, token, amount
FROM balances
WHERE (chain_id, address) IN ((1, '0x9c2e4f1a'), (10, '0x4b1e7c2a'));
```

A row-value `IN` matches whole tuples. When a row-value list covers the shard
key, only the shards that own the listed tuples are read.

## GROUP BY, HAVING and DISTINCT

```sql
SELECT token, COUNT(*) AS n
FROM transfers
WHERE chain_id = 1
GROUP BY token;
```

```sql
SELECT token, COUNT(*)
FROM transfers
WHERE chain_id = 1
GROUP BY token
HAVING COUNT(*) > 4;
```

```sql
SELECT DISTINCT token
FROM balances
WHERE chain_id = 1;
```

`GROUP BY` takes column names, output positions (`GROUP BY 1`), or an expression
that matches one `SELECT` output. Every non-aggregate output is grouped.
`HAVING` filters groups by their aggregate outputs.

## ORDER BY, LIMIT and OFFSET

`ORDER BY` names `SELECT` outputs, each with `ASC` or `DESC` and optionally
`NULLS FIRST` or `NULLS LAST`. `LIMIT` takes a positive integer; `OFFSET` an
unsigned integer.

```sql
SELECT address, amount
FROM balances
WHERE chain_id = 1 AND token = 'USDC'
ORDER BY amount DESC
LIMIT 10 OFFSET 10;
```

Derived tables and subqueries nest anywhere a relation or a value is expected:

```sql
SELECT token, total
FROM (SELECT token, SUM(amount) AS total FROM balances GROUP BY token) totals
WHERE total > 0;
```

## AS OF COMMIT

On a `VERSIONED` table, `AS OF COMMIT <n>` reads the rows as they stood at
commit `n`:

```sql
SELECT address, token, amount
FROM balances AS OF COMMIT 42
WHERE chain_id = 1;
```

See [Versioned tables](/docs/concepts/versioned-tables).

## Set operations

`UNION`, `INTERSECT` and `EXCEPT`, each with `ALL` or `DISTINCT` (the default),
combine `SELECT` statements whose outputs match. `INTERSECT` binds tighter than
`UNION`.

```sql
SELECT sender AS address
FROM transfers
UNION
SELECT recipient AS address
FROM transfers;
```

## WITH

`WITH <name> AS (<select>) <select>` names a query for the statement that
follows it:

```sql
WITH recent AS (
  SELECT token, amount
  FROM transfers
  WHERE transferred_at > '2026-10-01 00:00:00.000'
)
SELECT token, SUM(amount) AS volume
FROM recent
GROUP BY token;
```

## Parameters

A `?` marks a parameter whose value is sent beside the statement:

```sql
SELECT address, amount
FROM balances
WHERE chain_id = ? AND token = ?
LIMIT 100;
```

## Windows

Window functions take `OVER (PARTITION BY … ORDER BY …)`, and time windows
(`TUMBLE`, `HOP`, `SESSION`) group [streaming queries](/docs/sql/streaming).

---

Source: https://nyxdb.io/docs/sql/select/