SELECT keyword
SELECT
allows you to specify a list of columns and expressions to be selected
and evaluated from a table.
#
Syntaxtip
The table
can either be in your database (in which case you would pass the
table's name), or the result of a sub-query.
#
Simple select#
All columnsQuestDB supports 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
#
Specific columnsTo select specific columns, replace * by the names of the columns you are interested in.
Example:
#
Arithmetic expressionsSELECT
is capable of evaluating multiple expressions and functions. You can
mix comma separated lists of expressions with the column names you are
selecting.
The result of rating > 3.5
is a boolean. The column will be named good and
take values true or false.
#
AliasesUsing 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
note
Alias names and column names must be unique.
#
Aggregationinfo
Supported aggregation functions are listed on the aggregation reference.
#
Aggregation by groupQuestDB evaluates aggregation functions without need for traditional GROUP BY
.
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
functions.
#
Aggregation arithmeticAggregation functions can be used in arithmetic expressions. The following
computes mid
of rating values for every movie.
tip
Whenever possible, it is recommended to perform arithmetic outside
of
aggregation functions as this can have a dramatic impact on performance. For
example, min(value/2)
is going to execute considerably more slowly than
min(value)/2
, although both return the same result.
#
Supported clausesQuestDB supports the following standard SQL clauses within SELECT statements.
#
CASEConditional results based on expressions.
#
SyntaxFor more information, please refer to the CASE reference
#
CASTConvert values and expression between types.
#
SyntaxFor more information, please refer to the CAST reference
#
DISTINCTReturns distinct values of the specified column(s).
#
SyntaxFor more information, please refer to the DISTINCT reference.
#
FILLDefines filling strategy for missing data in aggregation queries. This function complements SAMPLE BY queries.
#
SyntaxFor more information, please refer to the FILL reference.
#
JOINJoin tables based on a key or timestamp.
#
SyntaxFor more information, please refer to the JOIN reference
#
LIMITSpecify the number and position of records returned by a query.
#
SyntaxFor more information, please refer to the LIMIT reference.
#
ORDER BYOrders the results of a query by one or several columns.
#
SyntaxFor more information, please refer to the ORDER BY reference
#
UNION, EXCEPT & INTERSECTCombine the results of two or more select statements. Can include or ignore duplicates.
#
SyntaxFor more information, please refer to the UNION, EXCEPT & INTERSECT reference
#
WHEREFilters query results
#
SyntaxQuestDB 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 clausesQuestDB augments SQL with the following clauses.
#
LATEST ONRetrieves the latest entry by timestamp for a given key or combination of keys This function requires a designated timestamp.
#
SyntaxFor more information, please refer to the LATEST ON reference.
#
SAMPLE BYAggregates time series data into homogeneous time chunks. For example daily average, monthly maximum etc. This function requires a designated timestamp.
#
SyntaxFor more information, please refer to the SAMPLE BY reference.
#
TIMESTAMPDynamically 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.
caution
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.
#
SyntaxFor more information, refer to the TIMESTAMP reference