For scenarios where multiple time series are stored in the same table, it is
relatively difficult to identify the latest items of these time series with
standard SQL syntax. QuestDB introduces
LATEST ON clause for a
SELECT statement to remove boilerplate
clutter and splice the table with relative ease.
columnNameused in the
LATEST ONpart of the clause is a
columnNamelist used in the
PARTITION BYpart of the clause is a list of columns of one of the following types:
LATEST ON is used as part of a SELECT statement
for returning the most recent records per unique time series identified by the
PARTITION BY column values.
To illustrate how
LATEST ON is intended to be used, consider the
trips table in the QuestDB demo instance. This
table has a
payment_type column as
SYMBOL type which specifies the method of
payment per trip. We can find the most recent trip for each unique method of
payment with the following query:
The above query returns the latest value within each time series stored in the
table. Those time series are determined based on the values in the column(s)
specified in the
PARTITION BY part of the
LATEST ON clause. In our example
those time series are represented by different payment types. Then the column
used in the
LATEST ON part of the clause stands for the designated timestamp
column for the table. This allows the database to find the latest value within
each time series.
The below sections will demonstrate other ways to use the
LATEST ON clause.
You can also write this query with the old syntax:
LATEST BY syntax is considered deprecated. While it's still supported
by the database, you should use the new
LATEST ON PARTITION BY syntax in your
applications. There are two key requirements when using the new syntax:
- A timestamp column must always be specified
LATEST ONhas to follow the
WHEREclause. In the old syntax, it was vice versa.
LATEST ON, a timestamp column used in the
LATEST ON part needs to be
specified as a designated timestamp. More information can be found in the
designated timestamp page for specifying
this at table creation or at query time.
For the next examples, we can create a table called
balances with the
This provides us with a table with the following content:
LATEST ON is provided a single column of the type
SYMBOL, the query will
end after all distinct symbol values are found.
The query returns two rows with the most recent records per unique
When multiple columns are specified in
LATEST ON queries, the returned results
are the most recent unique combinations of the column values. This example
LATEST ON customer ID and balance currency:
The results return the most recent records for each unique combination of
For a single
SYMBOL column, QuestDB will know all distinct values upfront and
stop scanning table contents once the latest entry has been found for each
distinct symbol value. When
LATEST ON is provided multiple columns, QuestDB
has to scan the entire table to find distinct combinations of column values.
Although scanning is fast, performance will degrade on hundreds of millions of
records. If there are multiple columns in the
LATEST ON clause, this will
result in a full table scan.
For this example, we can create another table called
the following SQL:
Note that this table doesn't have a designated timestamp column and also
contains time series that are unordered by
Due to the absent designated timestamp column, we can't use
LATEST ON directly
on this table, but it's possible to use
LATEST ON over a sub-query:
Just like with the
balances table, the query returns two rows with the most
recent records per unique
The following queries illustrate how to change the execution order in a query by using brackets.
This query executes
LATEST ON and returns the most recent
balance which is above 800. The execution order is as follows:
- filter out all balances below 800
- find the latest balance by
This query executes
LATEST ON before
WHERE and returns the most recent
records, then filters out those below 800. The steps are:
- Find the latest balances by customer ID.
- Filter out balances below 800. Since the latest balance for customer 1 is equal to 330.5, it is filtered out in this step.
It's possible to combine a time-based filter with the balance filter from the
previous example to query the latest values for the
2020-04-21 date and filter
out those below 800.
Since QuestDB allows you to omit the
SELECT * FROM part of the query,
we omitted it to keep the query compact.
Such a combination is very powerful since it allows you to find the latest values for a time-slice of the data and then apply a filter to them in a single query.