SELECT List

The SELECT list defines expressions that form the output rows of the SELECT statement. The expressions can refer to columns computed in the FROM clause.

Every output column of a SELECT statement has a name. In a simple SELECT statement, this name labels the column for display. When the SELECT is a subquery of a larger query, the name is the column name of the result set produced by the subquery. To define the name of an output column, use AS <output_name> after the column’s expression. To prevent conflicts with future keyword additions, use AS or double-quote the output name. If you don’t specify a column name, a name is chosen automatically. If the column’s expression is a simple column reference, the chosen name is the same as the column’s name. Complex queries use the function name, type name, or a number that specifies the order of the column in the result set.

You can refer to a column name in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses.

You can use * in the output list as a shorthand for all the columns of the selected rows. You can use table_name.* as a shorthand for the columns in the defined table. In these cases the output column names are the same as the table column names.

Data Cloud SQL evaluates output expressions after sorting and limiting if the expressions aren’t referenced in DISTINCT, ORDER BY, or GROUP BY. Output expressions that contain set-returning functions are evaluated after sorting and before limiting.

The SELECT clause can optionally contain the DISTINCT keyword. SELECT DISTINCT removes all duplicate rows from the result set.

SELECT DISTINCT ON ( expression [, ...] ) keeps the first row of each set of rows that match the expression. The DISTINCT ON expressions are interpreted using the same rules as ORDER BY. The first row of each set is random unless you use ORDER BY.

Match the DISTINCT ON expressions to the leftmost ORDER BY expressions. The ORDER BY clause normally contains additional expressions that determine the precedence of rows within each DISTINCT ON group.

This example shows a select statement selecting the location and time columns from weather_reports table. In addition, it also computes an additional column "need sunscreen?" based on the weather report.

This example shows a select statement with DISTINCT ON and ORDER BY defined. The example retrieves the most recent weather report for each location. Without ORDER BY the report returns a random time for each location.