Basic Statements

Updated on 2024-11-08 GMT+08:00

View PDF

Function

This statement is a basic query statement and is used to return the query results.

Syntax

    
       SELECT [ALL | DISTINCT] attr_expr_list FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_name_list]
  [ORDER BY col_name_list][ASC | DESC]
  [CLUSTER BY col_name_list | DISTRIBUTE BY col_name_list]
  [SORT BY col_name_list]]
  [LIMIT number];

Keywords

**Table 1** SELECT keywords
Parameter	Description
ALL	The keyword ALL is used to return all matching rows from a database, including duplicates. It can only be followed by an asterisk (); otherwise, an error will occur. This is the default behavior in SQL statements and is typically not written explicitly. If neither ALL* nor DISTINCT is specified, the query result will include all rows, even if they are duplicates.
DISTINCT	When the DISTINCT keyword is used in a SELECT statement, the system removes duplicate data from the query results to ensure uniqueness.
WHERE	Specifies the filter criteria for a query. Arithmetic operators, relational operators, and logical operators are supported.
where_condition	Filter criteria.
GROUP BY	Specifies the grouping field. Single-field grouping and multi-field grouping are supported.
col_name_list	Field list
ORDER BY	Sort the query results.
ASC/DESC	ASC sorts from the lowest value to the highest value. DESC sorts from the highest value to the lowest value. ASC is the default sort order.
CLUSTER BY	CLUSTER BY is used to bucket the table according to the bucketing fields and then sort within the bucketed table. If the field of DISTRIBUTE BY is the same as the field of SORT BY and the sorting is in descending order, the combination of DISTRIBUTE BY and SORT BY achieves the same function as CLUSTER BY.
DISTRIBUTE BY	Specifies the bucketing fields without sorting the table.
SORT BY	The objects will be sorted in the bucket.
LIMIT	LIMIT is used to limit the query results. Only INT type is supported by the number parameter.

Precautions

The table to be queried must already exist, or an error message will be displayed.
When submitting SQL statements to display binary data on the DLI management console, the binary data is converted to Base64.

Example

To filter the record, in which the name is Mike, from the student table and sort the results in ascending order of score, run the following statement:

    
       SELECT * FROM student
  WHERE name = 'Mike'
  ORDER BY score;

Parent topic: SELECT

Previous topic: SELECT

Next topic: Sort

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot