2.13 Supported SELECT Statement Clauses

This section provides information related to the SELECT statement clauses supported in HeatWave and describes how you can use them GROUP BY clause and QUALIFY clause, to group or filter the processing results.

Before You Begin

• Review the requirements.

• Connect to your MySQL DB System.

• Verify if the query will be offloaded to HeatWave for processing.

SELECT STATEMENT Clauses

The following SELECT statement clauses are supported in the HeatWave secondary engine.

• GROUP BY clause

  • A CUBE modifier in addition to the WITH ROLLUP modifier. CUBE modifier is available only in secondary engine and is not supported in the DB System.

  • A ROLLUP modifier as a preferred alternative to the WITH ROLLUP modifier.

• QUALIFY clause is added between the WINDOW clause and the ORDER BY clause. QUALIFY clause is available only in secondary engine and is not supported in the DB System.

  In addition to constraints similar to the HAVING clause, the QUALIFY clause can also include predicates related to a window function.

  Similar to the HAVING clause, the QUALIFY clause can refer to aliases mentioned in the SELECT list.

  The QUALIFY clause requires the inclusion of at least one window function in the query. The window function can be part of any one of the following:

  • The SELECT column list.

  • A filter predicate of the QUALIFY clause.

• TABLESAMPLE clause is available only in secondary engine and is not supported in the DB System.

SELECT STATEMENT Syntax

The following SELECT statement clauses are supported in the HeatWave secondary engine.

Press CTRL+C to copy
 
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [STRAIGHT_JOIN]
    [SQL_BUFFER_RESULT]
    select_expr [, select_expr] ...
    [FROM table_references
      [PARTITION partition_list]
	  [TABLESAMPLE { SYSTEM | BERNOULLI } (sample_percentage)]]
    [WHERE where_condition]
    [GROUP BY {{col_name | expr | position}, ... [WITH ROLLUP]
	        | {CUBE | ROLLUP} ({col_name | expr | position}, ...)}]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [QUALIFY qualify_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

For a full explanation of the SELECT syntax, see: SELECT Statement.