Distinct/Group by Option (When No Aggregation and Not Table Key)

The Distinct/Group by Option property controls the generation of DISTINCT or GROUP BY in the SELECT SQL statement. You can select from the following options:

• Use DISTINCT (default)
• No DISTINCT, no GROUP BY
• Use GROUP BY

  If you are using a Vertica database that includes correlated subqueries, to support the use of the Use GROUP By option listed above, you must also define the Sub Query Type VLDB property (see Optimizing Queries) to use either of the following options:

  Use Temporary Table, falling back to EXISTS (SELECT *...) for correlated subquery

  Use Temporary Table, falling back to IN (SELECT COL) for correlated subquery

Upon selecting an option, a sample SQL statement shows the effect that each option has.

The SQL Engine ignores the option selected for this property in the following situations:

• If there is aggregation, GROUP BY is used without the use of DISTINCT.
• If there is no attribute (only metrics), DISTINCT is not used.
• If there is COUNT (DISTINCT …) and the database does not support this functionality, a SELECT DISTINCT pass of SQL is used, which is followed by a COUNT(*) pass of SQL.
• If the database does not allow DISTINCT or GROUP BY for certain column data types, DISTINCT and GROUP BY are not used.
• If the select level is the same as the table key level and the table's true key property is selected, DISTINCT is not used.

When none of the above conditions are met, the option selected for this property determines how DISTINCT and GROUP BY are used in the SQL statement.

Levels at Which You Can Set This

Database instance, report, and template