SELECT command to retrieve rows from a table or materialized view. It returns the rows that satisfy the creteria that you specify with the clauses and conditions in your query.
SELECT [ ALL | DISTINCT ] [ * | expression [ AS output_name ] [ , expression [ AS output_name ] ... ] ]
[ FROM from_item [ , from_item ...] ]
[ WHERE condition ]
[ GROUP BY grouping_expression [ , grouping_expression ... ] ]
[ HAVING condition ]
[ ORDER BY sort_expression [ ASC | DESC ] [ , ... ] ]
[ LIMIT count_number ]
[ OFFSET start [ ROW | ROWS ] ];
from_item can be:
table_name [ [ AS ] alias [ ( column_alias_list ) ] ]
window_type ( table_name, col_name, interval_expression ) [ [ AS ] alias [ ( column_alias_list ) ] ]
( SELECT ) [ [ AS ] alias [ ( column_alias_list ) ] ]
from_item join_type from_item [ ON join_condition ]
|Parameter or clause||Description|
|expression||A column or an expression.|
|alias||A temporary alternative name for a table or materialized view in a query.|
|table_name||A table or materialized view.|
Values can be:
|ORDER BY clause||The default sort order is ASC. Nulls options are not supported now. If the sort order is ASC or unspecified, nulls will be placed in front of non-null values. If the sort order is DESC, nulls will be placed after non-null values. This is different from the sort logic in PostgreSQL.|
Values can be:
|LIMIT clause||When the ORDER BY clause is not present, the LIMIT clause cannot be used as part of a materialized view.|
|count_number||The number of results you want to get.|
|OFFSET clause||The OFFSET clause can only be used with the LIMIT and ORDER BY clauses.|
|(SELECT)||A SELECT command. You must enclose the subquery in parentheses, and specify an alias. When you include a subquery in the FROM clause, the output of the subquery is used as a temporary view that is only valid in the query.|
Supported join types:
Currently, only the ON clause is supported for joins.
|join_condition||Conditions for the ON clause that must be met before the two from_items can be joined.|
|window_type||The type of the time window function. Possible values are |
|interval_expression||The interval expression, in the format of |
Below are the tables within the same schema that we will be writing queries from.
taxi_trips includes the columns
id identifies each unique trip.
"distance": DOUBLE PRECISION,
"duration": DOUBLE PRECISION
taxi includes the columns
taxi_trips are matching fields.
company includes the columns
taxi are matching fields.
The following query selects the total distance and duration for each taxi from the company "Yellow Taxi".
sum(trips.distance) as total_distance,
sum(trips.duration) as total_duration
FROM taxi_trips as trips
LEFT JOIN taxi ON trips.id = taxi.trip_id
WHERE taxi_id IN
WHERE company_id = 'Yellow Taxi')
GROUP BY taxi_id
ORDER BY total_distance, total_duration;