SELECT keyword

SELECT allows you to specify a list of columns and expressions to be selected and evaluated from a table.

tip

Looking for SELECT best practices? Checkout our Maximize your SQL efficiency: SELECT best practices blog.

Syntax​

Flow chart showing the syntax of the SELECT keyword

Note: table can either a specified table in your database or passed forward as the result of a sub-query.

Simple select​

All columns​

QuestDB 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

QuestDB dialect
ratings;
Traditional SQL equivalent
SELECT * FROM ratings;

Specific columns​

To select specific columns, replace * by the names of the columns you are interested in.

Example:

SELECT movieId, rating FROM ratings;

Arithmetic expressions​

SELECT is capable of evaluating multiple expressions and functions. You can mix comma separated lists of expressions with the column names you are selecting.

SELECT movieId, (100 - rating)*2, rating > 3.5 good
FROM ratings;

The result of rating > 3.5 is a boolean. The column will be named good and take values true or false.

Aliases​

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

note

Alias names and column names must be unique.

SELECT movieId alias1, rating alias2
FROM ratings

Aggregation​

Supported aggregation functions are listed on the aggregation reference.

Boolean expressions​

Supports AND/OR, NOT & XOR.

AND and OR​

AND returns true if both operands are true, and false otherwise.

OR returns true if at least one of the operands is true.

SELECT
    (true AND false) AS and_col,
    (true OR false) AS or_col;

NOT​

NOT inverts the truth value of the operand.

SELECT
    NOT (true AND false) AS not_col;

XOR​

^ is the bitwise XOR operator. When applied to the integers cast from booleans, it returns 1 (true) if the bits are different, and 0 (false) if they are the same. The result is then cast back to boolean for readability:

SELECT
    (true::int ^ false::int)::boolean AS xor_col1,
    (true::int ^ true::int)::boolean AS xor_col2;

Aggregation by group​

QuestDB 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.

QuestDB dialect
SELECT movieId, avg(rating), count()
FROM ratings;
Traditional SQL equivalent
SELECT movieId, avg(rating), count()
FROM ratings
GROUP BY movieId;

Aggregation arithmetic​

Aggregation functions can be used in arithmetic expressions. The following computes mid of rating values for every movie.

SELECT movieId, (min(rating) + max(rating))/2 mid, count() count
FROM ratings;
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 clauses​

QuestDB supports the following standard SQL clauses within SELECT statements.

CASE​

Conditional results based on expressions.

Syntax​

Flow chart showing the syntax of CASE

For more information, please refer to the CASE reference

CAST​

Convert values and expression between types.

Syntax​

Flow chart showing the syntax of the CAST keyword

For more information, please refer to the CAST reference

DISTINCT​

Returns distinct values of the specified column(s).

Syntax​

Flow chart showing the syntax of the DISTINCT keyword

For more information, please refer to the DISTINCT reference.

FILL​

Defines filling strategy for missing data in aggregation queries. This function complements SAMPLE BY queries.

Syntax​

Flow chart showing the syntax of the FILL keyword

For more information, please refer to the FILL reference.

JOIN​

Join tables based on a key or timestamp.

Syntax​

Flow chart showing the syntax of the high-level syntax of the JOIN keyword

For more information, please refer to the JOIN reference

LIMIT​

Specify the number and position of records returned by a query.

Syntax​

Flow chart showing the syntax of the LIMIT keyword

For more information, please refer to the LIMIT reference.

ORDER BY​

Orders the results of a query by one or several columns.

Syntax​

Flow chart showing the syntax of the ORDER BY keyword

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.

Syntax​

Flow chart showing the syntax of the UNION, EXCEPT & INTERSECT keyword

For more information, please refer to the UNION, EXCEPT & INTERSECT reference

WHERE​

Filters query results

Syntax​

Flow chart showing the syntax of the WHERE clause

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.

LATEST ON​

Retrieves the latest entry by timestamp for a given key or combination of keys This function requires a designated timestamp.

Syntax​

Flow chart showing the syntax of the LATEST ON keyword

For more information, please refer to the LATEST ON reference.

SAMPLE BY​

Aggregates time-series data into homogeneous time chunks. For example daily average, monthly maximum etc. This function requires a designated timestamp.

Syntax​

Flow chart showing the syntax of the SAMPLE BY keyword

For more information, please refer to the SAMPLE BY reference.

TIMESTAMP​

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.

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.

Syntax​

Flow chart showing the syntax of the timestamp function

For more information, refer to the TIMESTAMP reference