SELECT allows you to specify a list of columns and expressions to be selected
and evaluated from a table.
table can either be in your database (in which case you would pass the
table's name), or the result of a sub-query.
SELECT * FROM tablename. When selecting all, you can also
omit most of the statement and pass the table name.
The two examples below are equivalent
To select specific columns, replace * by the names of the columns you are interested in.
SELECT is capable of evaluating multiple expressions and functions. You can
mix comma separated lists of expressions with the column names you are
The result of
rating > 3.5 is a boolean. The column will be named good and
take values true or false.
Using aliases allow you to give expressions or column names of your choice. You can assign an alias to a column or an expression by writing the alias name you want after that expression
Alias names and column names must be unique.
Supported aggregation functions are listed on the aggregation reference.
#Aggregation by group
QuestDB evaluates aggregation functions without need for traditional
Use a mix of column names and aggregation functions in a
SELECT clause. You
can have any number of discrete value columns and any number of aggregation
Aggregation functions can be used in arithmetic expressions. The following
mid of rating values for every movie.
Whenever possible, it is recommended to perform arithmetic
aggregation functions as this can have a dramatic impact on performance. For
min(value/2) is going to execute considerably more slowly than
min(value)/2, although both return the same result.
QuestDB supports the following standard SQL clauses within SELECT statements.
Conditional results based on expressions.
For more information, please refer to the CASE reference
Convert values and expression between types.
For more information, please refer to the CAST reference
Returns distinct values of the specified column(s).
For more information, please refer to the DISTINCT reference.
Defines filling strategy for missing data in aggregation queries. This function complements SAMPLE BY queries.
For more information, please refer to the FILL reference.
Join tables based on a key or timestamp.
For more information, please refer to the JOIN reference
Specify the number and position of records returned by a query.
For more information, please refer to the LIMIT reference.
Orders the results of a query by one or several columns.
For more information, please refer to the ORDER BY reference
#UNION, EXCEPT & INTERSECT
Combine the results of two or more select statements. Can include or ignore duplicates.
For more information, please refer to the UNION, EXCEPT & INTERSECT reference
Filters query results
QuestDB supports complex WHERE clauses along with type-specific searches. For more information, please refer to the WHERE reference. There are different syntaxes for text, numeric, or timestamp filters.
#Additional time series clauses
QuestDB augments SQL with the following clauses.
Retrieves the latest entry by timestamp for a given key or combination of keys This function requires a designated timestamp.
For more information, please refer to the LATEST ON reference.
Aggregates time series data into homogeneous time chunks. For example daily average, monthly maximum etc. This function requires a designated timestamp.
For more information, please refer to the SAMPLE BY reference.
Dynamically creates a designated timestamp on the output of a query. This allows to perform timestamp operations like SAMPLE BY or LATEST ON on tables which originally do not have a designated timestamp.
The output query must be ordered by time.
TIMESTAMP() does not check for order
and using timestamp functions on unordered data may produce unexpected results.
For more information, refer to the TIMESTAMP reference