LATEST BY finds the latest entry by timestamp for a given key or combination of keys as part of a SELECT statement.

To find the latest values, QuestDB will search time series from the newest values to the oldest.

• For single SYMBOL columns, QuestDB will know all distinct values upfront and stop once the latest entry has been found for each symbol value.
• For other field types, or multiple fields, QuestDB will scan the entire time series. Although scan is very fast this means the performance will degrade on hundreds of millions of records for non-symbol keys.

:::note

To use LATEST BY, one column needs to be designated as timestamp. Find out more in the designated timestamp section.

:::

Syntax

:::note

By default, QuestDB executes where clauses before latest by. To execute where after latest by, you need to use sub-queries using brackets. You can learn how to do this in the examples.

:::

Examples

Single column

LATEST BY can be used with single columns. When this column is of type SYMBOL, the query will end as soon as all distinct symbol values have been found.

SELECT city, temperature
FROM weather
LATEST BY city;

Multiple columns

LATEST BY can also reference multiple columns although this can be slower.

SELECT cust_id, balance_ccy, balance
FROM balances
LATEST BY cust_id, balance_ccy;

Execution order

The below queries illustrate how to change the execution order in a query by using brackets. Assume the following table

WHERE first

SELECT * FROM balances LATEST BY cust_id, balance_ccy
WHERE balance > 800;

This query executes WHERE before LATEST BY. It will return the latest balance which is above 800. The steps are:

• Filter out all balances below 800.
• Finds the latest balance for each combination of cust_id and balance_ccy.

Since the latest USD balance for customer 1 is equal to 330.5, it is filtered out in the first step. Therefore, the returned balance is 1500, which is the latest possible balance above 800.

LATEST BY first

(SELECT * FROM balances LATEST BY cust_id, balance_ccy) --note the brackets
WHERE balance > 800;

This query executes LATEST BY before WHERE. It returns the latest balances, then filters out those below 800. The steps are

• Find the latest balances, regardless of value
• Filter out balances below 800. Since the latest balance for customer 1 is equal to 330.5, it is filtered out in the second step.