# Writing queries

Patterns for querying the Nominal SQL interface: how to scope a query, filter and aggregate it, work across tables, and bound what comes back.

## Scoping telemetry queries

Every query against a [telemetry table](https://dev.nominal.io/sql-reference/schema.md#telemetry-tables) must filter by the dataset(s) it reads. The telemetry tables are `points_double`, `points_int`, `points_string`, `points_struct`, `logs`, and `channels`, and each requires a `dataset_rid` predicate, or a join on `dataset_rid` to a metadata table as shown under [Joins](https://dev.nominal.io/sql-guides/writing-queries.md#joins):

```sql
SELECT ts, channel, value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
```

or

```sql
SELECT ts, channel, value
FROM points_double
WHERE dataset_rid IN ('<dataset-rid>', '<dataset-rid>')
```

Omitting it fails validation:

```text
Query on table 'points_double' must filter by an entity selector: [dataset_rid]; apply the filter in the SELECT that reads this table
```

The [metadata tables](https://dev.nominal.io/sql-reference/schema.md#metadata-tables) (`assets`, `runs`, `run_assets`, `datasets`, `events`, and `event_assets`) carry no such requirement, so start from [`datasets`](https://dev.nominal.io/sql-reference/schema/datasets.md) when you need a `dataset_rid`:

```sql
SELECT dataset_rid, name FROM datasets LIMIT 20
```

## Reading a whole dataset

To read every channel in a dataset, whatever its type, use the dataset’s RID in double quotes as the table name. No additional `WHERE dataset_rid = ...` filter is needed:

```sql
SELECT *
FROM "<ri.catalog.instance.dataset.identifier>"
ORDER BY ts
LIMIT 100
```

Each row holds its value in the column for its `data_type`: `value_double`, `value_int`, `value_string`, `value_struct`, or `message` for a log entry.

## Filtering

Filter on time by comparing `ts` against `TIMESTAMP` literals:

```sql
SELECT ts, value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
  AND ts >= TIMESTAMP '2026-07-17 21:00:00'
  AND ts <  TIMESTAMP '2026-07-17 22:00:00'
ORDER BY ts
```

Two things to watch:

- **String comparison is case-sensitive.** `channel = 'Vehicle_Speed'` does not match a channel recorded as `vehicle_speed`. Wrap both sides in `UPPER` when you’re unsure of the casing, at the cost of a slower filter.
- **`value` is nullable** on every `points_*` table. A sample can exist with no value, so add `AND value IS NOT NULL` when nulls would distort what you’re computing.

## Filtering by tag

Where the same channel name appears for several physical sources, a channel filter alone is ambiguous. The `points_*` and `logs` tables each carry a `tags` column of type `MAP`, holding the key-value pairs recorded alongside the series, and indexing it by key narrows the query to one source.

Index a tag by key and compare it like any other value:

```sql
SELECT ts, value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
  AND tags['<tag key>'] = '<tag value>'
ORDER BY ts
```

Tag keys vary between datasets. To see which keys a dataset uses, list its channels and their tags from [`channels`](https://dev.nominal.io/sql-reference/schema/channels.md):

```sql
SELECT channel, tags
FROM channels
WHERE dataset_rid = '<dataset-rid>'
```

> **Note**
>
> Tag filtering is not a substitute for `dataset_rid`. A `tags` predicate does not satisfy the scoping requirement, so telemetry queries need both.

## Aggregation

Aggregating turns millions of samples into something you can read or plot without pulling every row across the wire. To downsample, bucket each timestamp with `DATE_BIN` and group by the bucket:

```sql
SELECT DATE_BIN(INTERVAL '1' MINUTE, ts, TIMESTAMP '1970-01-01 00:00:00') AS bucket,
       AVG(value) AS mean_value,
       MIN(value) AS min_value,
       MAX(value) AS max_value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
  AND channel = '<channel name>'
GROUP BY bucket
ORDER BY bucket
```

The third argument is the origin the buckets are measured from. Passing the Unix epoch gives you boundaries aligned to clock time.

To get one column’s value at the row where another is highest or lowest, such as each channel’s latest reading, use `MAX_BY` or `MIN_BY`:

```sql
SELECT channel,
       MAX_BY(value, ts) AS last_value,
       MAX(ts) AS last_seen
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
GROUP BY channel
ORDER BY channel
```

Beyond these, the aggregates include standard deviation, variance, root mean square, and continuous percentiles. See [Aggregate functions](https://dev.nominal.io/sql-reference/schema/function-reference.md#aggregate-functions) for the full list.

## Joins

Joins pair telemetry with the metadata that describes it. `channels` and `datasets` both carry a `dataset_rid` to match on:

```sql
SELECT d.name, c.channel, c.data_type
FROM channels AS c
JOIN datasets AS d ON c.dataset_rid = d.dataset_rid
WHERE c.dataset_rid = '<dataset-rid>'
  AND d.dataset_rid = '<dataset-rid>'
ORDER BY c.channel
```

**Filter the metadata side too.** The join condition doesn’t narrow what is read from `datasets`, so with only `c.dataset_rid` filtered, the query reads every dataset in the workspace and can fail on the [metadata limit](https://dev.nominal.io/sql-reference/rest-api/query.md#limits). In any join between telemetry and metadata tables, filter the metadata table directly.

**Find datasets by name and read them in one query.** Instead of looking up a `dataset_rid` first, join to `datasets` on `dataset_rid` and filter on the dataset’s name. The join takes the place of the `dataset_rid` filter:

```sql
SELECT d.name, p.ts, p.channel, p.value
FROM points_double AS p
JOIN datasets AS d ON p.dataset_rid = d.dataset_rid
WHERE d.name = '<dataset name>'
ORDER BY p.ts
LIMIT 100
```

Every dataset that matches the filter is read, so keep the filter specific.

Metadata tables join to each other the same way. `run_assets` connects runs to the assets they cover:

```sql
SELECT r.title, a.title AS asset
FROM run_assets AS ra
JOIN runs AS r ON ra.run_rid = r.run_rid
JOIN assets AS a ON ra.asset_rid = a.asset_rid
ORDER BY r.start_time DESC
LIMIT 20
```

To read a run’s data, go through the run’s assets: `runs` has no `dataset_rid` column, but `run_assets` connects a run to its assets, and each asset’s `dataset_rid` names its dataset. Filtering `ts` to the run’s start and end keeps only what was recorded during the run:

```sql
SELECT a.title AS asset, p.ts, p.channel, p.value
FROM points_double AS p
JOIN assets AS a ON p.dataset_rid = a.dataset_rid
JOIN run_assets AS ra ON ra.asset_rid = a.asset_rid
JOIN runs AS r ON r.run_rid = ra.run_rid
WHERE r.run_rid = '<run-rid>'
  AND p.ts BETWEEN r.start_time AND r.end_time
ORDER BY p.ts
LIMIT 100
```

For a run that’s still open, which has no `end_time`, use `p.ts >= r.start_time` instead.

### Combining telemetry tables

Telemetry tables combine through `IN` subqueries and `PIVOT` rather than `JOIN`.

To filter one telemetry table by another, use an `IN` subquery. Each read needs its own `dataset_rid` filter, including the one inside the subquery:

```sql
SELECT ts, channel, value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
  AND channel IN (
    SELECT channel FROM <telemetry table>
    WHERE dataset_rid = '<dataset-rid>' AND <condition>
  )
ORDER BY ts
```

To compare channels side by side, `PIVOT` a subquery so each channel becomes a column. Bucket `ts` with `DATE_BIN` so samples from different channels land in the same row, and pick each bucket’s last reading with `MAX_BY(value, ts)`:

```sql
SELECT *
FROM (
  SELECT DATE_BIN(INTERVAL '1' SECOND, ts, TIMESTAMP '1970-01-01 00:00:00') AS bucket,
         channel, ts, value
  FROM points_double
  WHERE dataset_rid = '<dataset-rid>'
) PIVOT (MAX_BY(value, ts) FOR channel IN ('<channel name>' AS <column name>, '<channel name>' AS <column name>))
ORDER BY bucket
```

## Limiting results

Add a `LIMIT` while exploring, since a dataset can hold millions of samples. The service also limits how large a result can be and how long a query can run. See [Limits](https://dev.nominal.io/sql-reference/rest-api/query.md#limits).

## Function availability

The [Function reference](https://dev.nominal.io/sql-reference/schema/function-reference.md) lists the functions queries can call, with their signatures. It also marks the ones that work only on telemetry tables, such as `DATE_BIN`, `MIN_BY`, and `DERIVATIVE`.