Single-table queries|OceanBase Cloud |docs|OceanBase Documentations

This topic describes how to use SQL statements to perform single-table queries in OceanBase Cloud.

Prerequisites

You have connected to an Oracle-compatible tenant of OceanBase Cloud. For more information about how to connect to a database, see Overview of connection methods.
You have the SELECT privilege. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Manage tenant accounts.

Syntax

Use the SELECT statement to query data.

The general syntax of a single-table query using the SELECT statement is as follows:

SELECT [ALL | DISTINCT] select_list FROM table_name
              [ WHERE query_condition ]
              [ GROUP BY group_by_expression ]
              [ HAVING group_condition ]
              [ ORDER BY column_list ][ASC | DESC]
              [ Row_Limiting_Clause ]

column_list:
 column_name[,column_name...]

Parameters

Parameter	Description
select_list	The list of columns to retrieve, which can be column names, expressions, or aggregate functions. Separate multiple columns with commas (,).
table_name	The name of the table from which data is to be retrieved.
WHERE query_condition	Optional. The query condition. Only rows that meet the condition will be returned.
GROUP BY group_by_condition	Optional. Specifies to group the results by the specified column. This parameter is typically used with aggregate functions.
HAVING group_condition	Optional. Specifies to filter the grouped result set. Only groups that meet the condition will be returned.
ORDER BY column_list	Optional. Specifies to sort the result set. You can specify to sort one or more columns.
ASC \| DESC	Optional. The order of sorting. `ASC` indicates the ascending order, and `DESC` indicates the descending order. The default value is `ASC`.
LIMIT limit_clause	Optional. Specifies to limit the number of rows returned for a query in pagination. You can specify the offset and the number of rows or percentage of rows to return. This parameter can be used with the `ORDER BY` clause to ensure the sorting order and obtain consistent results.
column_list	The list of columns to retrieve. You can specify one or more columns. Separate multiple columns with commas (,).
column_name	The name of the column to retrieve.

Execution order of keywords in the SELECT statement

If you use the WHERE, GROUP BY, HAVING, and ORDER BY clauses in a query, these clauses are executed in strict accordance with the following sequence:

The FROM clause is executed to find the required table.
The WHERE clause is executed to specify conditions.
The GROUP BY clause is executed to group records. If GROUP BY is not executed, all records are considered a group.
The HAVING clause is executed to filter the grouped results.
The SELECT clause is executed.
The DISTINCT clause is executed to remove duplicate rows.
The ORDER BY clause is executed to sort the results in ascending or descending order.
The ROWNUM clause is executed to limit the number of records displayed per page.

Notice

The difference between WHERE and HAVING is that WHERE filters data before grouping, while HAVING filters data after grouping and returns the entire SQL query result.

Create test tables and add test data to the tables

Create a table named dept.

CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(14),  
    location         VARCHAR(13),   
    CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);

Insert six records into the dept table.

INSERT INTO dept VALUES
(20,'Finance','beijing'),
(35,'Administration','hangzhou'),
(40,'Development','xian'),    
(30,'Workshop','guangzhou'),    
(25,'Legal affairs','shanghai'),    
(45,'Office','suzhou');

Create a table named emp.

CREATE TABLE emp(  
    empno         NUMBER(4,0),  
    empname       VARCHAR(10),  
    job           VARCHAR(9),  
    mgr           NUMBER(4,0),  
    hiredate      DATE,  
    sal           NUMBER(7,2),  
    comm          NUMBER(7,2),        
    deptno        NUMBER(2,0),
    age           NUMBER(2,0),
    CONSTRAINT PK_emp PRIMARY KEY (empno),
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);

Insert six records into the emp table.

INSERT INTO emp VALUES
(1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22),
(1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22),
(1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22),    
(1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33),    
(1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33),    
(1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22);

Basic queries

Query all columns

An asterisk (*) specifies to return all fields in a table. Here is an example:

obclient [SYS]> SELECT * FROM emp;

The return result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
|  1566 | JONES   | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set

The following example is equivalent to the preceding one:

obclient [SYS]> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;

The return result is as follows:

+---------+-------+----------+------+-----------+------+------+--------+
| EMPNAME | EMPNO | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO |
+---------+-------+----------+------+-----------+------+------+--------+
| SMITH   |  1369 | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |
| ALLEN   |  1499 | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |
| JONES   |  1566 | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |
| BLAKE   |  1698 | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |
| SCOTT   |  1788 | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |
| FORD    |  1902 | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |
+---------+-------+----------+------+-----------+------+------+--------+
6 rows in set

Specify a table alias in a query

Query data in the emp table and set the table alias to t.

obclient [SYS]> SELECT t.* FROM (emp) t;

The return result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
|  1566 | JONES   | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set

Query specified columns

obclient [SYS]> SELECT empname,deptno FROM emp;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
6 rows in set

Set aliases for columns in a query

obclient [SYS]> SELECT empname AS Employee name, deptno AS Department No. FROM emp;

The return result is as follows:

+---------------+----------------+
| Employee name | Department No. |
+---------------+----------------+
| SMITH         |             20 |
| ALLEN         |             35 |
| JONES         |             40 |
| BLAKE         |             30 |
| SCOTT         |             25 |
| FORD          |             45 |
+---------------+----------------+
6 rows in set

Specify to remove duplicate rows in a query

obclient [SYS]> SELECT DISTINCT age FROM emp;

The return result is as follows:

+------+
| AGE  |
+------+
|   22 |
|   33 |
+------+
2 rows in set

Limit the number of rows returned by using the `ROWNUM` clause

obclient [SYS]> SELECT empname, deptno FROM emp WHERE ROWNUM <= 3;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
+---------+--------+
3 rows in set

Query calculated values

obclient [SYS]> SELECT empname, sal-100,job FROM emp;

The return result is as follows:

+---------+---------+----------+
| EMPNAME | SAL-100 | JOB      |
+---------+---------+----------+
| SMITH   |     700 | CLERK    |
| ALLEN   |    1500 | SALESMAN |
| JONES   |    2875 | MANAGER  |
| BLAKE   |    2750 | MANAGER  |
| SCOTT   |    2900 | ANALYST  |
| FORD    |    2900 | ANALYST  |
+---------+---------+----------+
6 rows in set

You can apply functions to specified columns in the query. For example, in the following sample code, the LOWER function is used to convert job to lowercase. For more information, see Use operators and functions in queries.

obclient [SYS]> SELECT empname, sal-100, LOWER(job) FROM emp;

The return result is as follows:

+---------+---------+------------+
| EMPNAME | SAL-100 | LOWER(JOB) |
+---------+---------+------------+
| SMITH   |     700 | clerk      |
| ALLEN   |    1500 | salesman   |
| JONES   |    2875 | manager    |
| BLAKE   |    2750 | manager    |
| SCOTT   |    2900 | analyst    |
| FORD    |    2900 | analyst    |
+---------+---------+------------+
6 rows in set

FROM FUNCTION queries

In the Oracle compatible mode of OceanBase Cloud, you can use the SELECT * FROM FUNCTION(*); syntax to call a table-valued function and use the results returned by the function as table data for queries.

When you use the SELECT * FROM FUNCTION(*); syntax, take note of the following considerations:

The function must return data of the table type.
The number and type of columns returned by the table function must match the number and type of columns required by the environment where the SELECT statement is executed.
The number of rows returned by the table function must be greater than or equal to the number of rows required by the environment where the SELECT statement is executed.
The number and type of parameters of the function must be the same as those defined in the function.

For more information about custom data types, see CREATE TYPE and User-defined subtypes.

For more information about how to create a function, see CREATE FUNCTION and Create a function.

Here is an example:

Define a table type emp_type.

obclient [SYS]> DELIMITER //
obclient [SYS]> CREATE OR REPLACE TYPE emp_type AS OBJECT (id NUMBER); //
Query OK, 0 rows affected

Define a set type emp_type_list, which is a list of emp_type objects.

obclient [SYS]> CREATE OR REPLACE TYPE emp_type_list IS TABLE OF emp_type; //
Query OK, 0 rows affected

Create a function get_emp_info whose return type is emp_type_list.

obclient [SYS]> CREATE OR REPLACE FUNCTION get_emp_info
        RETURN emp_type_list PIPELINED
    IS CURSOR emp_list_cursor IS SELECT EMPNO FROM emp;
        v_emp_id_type emp_type;
      v_emp_id varchar2(5);
      BEGIN
      OPEN emp_list_cursor;
      LOOP
      FETCH emp_list_cursor INTO v_emp_id;
      EXIT WHEN emp_list_cursor%notfound;
      v_emp_id_type : = emp_type(v_emp_id);
      PIPE ROW(v_emp_id_type);
      END LOOP;
      CLOSE emp_list_cursor;
      RETURN;
      END;//
Query OK, 0 rows affected

obclient [SYS]> DELIMITER ;

Query the results returned by the function.

obclient [SYS]> SELECT * FROM get_emp_info();

The return result is as follows:

+------+
| ID   |
+------+
| 1369 |
| 1499 |
| 1566 |
| 1698 |
| 1788 |
| 1902 |
+------+
6 rows in set

Data filtering

You can add a WHERE clause to the SELECT statement to query data that meets the specified conditions. The WHERE clause can contain one or more conditions for filtering data. Only data that meets the WHERE conditions will be returned. You can flexibly use query conditions based on specific requirements to filter and retrieve target data.

When you use the WHERE clause, make sure that the conditions are correct and appropriate operators are used.

The following table lists general query conditions specified by the WHERE clause.

Condition type	Predicate
Comparison condition	`=`, `>`, `<`, `>=`, `<=`, `!=`, and `<>`
Logical condition (multiple conditions supported in a query)	`AND`, `OR`, and `NOT`
Fuzzy condition (matching by characters)	`LIKE` and `NOT LIKE`
Interval condition (with a specified range)	`BETWEEN AND` and `NOT BETWEEN AND`
Condition with a specified set	`IN` and `NOT IN`
Condition related to NULL values	`IS NULL` and `IS NOT NULL`

Queries with comparison conditions

Equal to (=)

Returns data equal to the value in the specified column. If the value is a string, it needs to be enclosed in single quotation marks (' ') or double quotation marks (" ").

obclient [SYS]> SELECT empname, deptno FROM emp WHERE deptno = 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| BLAKE   |     30 |
+---------+--------+
1 row in set

obclient [SYS]> SELECT empname, deptno FROM emp WHERE empname = 'ALLEN';

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

Not equal to (<> and !=)

The not equal to operator includes two expressions: <> and !=. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE column_name <> const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name != const_value;

Here is an example:

obclient> SELECT empname, deptno FROM emp WHERE deptno <> 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno != 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 rows in set

Greater than (>) and less than (<)

The greater than operator (>) and the less than operator (<) compare numbers based on their values. If characters are compared, they are converted into their respective ASCII codes, and then the ASCII codes are compared from left to right. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE column_name < const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name > const_value;

Note

The greater than or equal to (>=) and less than or equal to (<=) operators operate in a similar manner.

Here are some examples:

obclient> SELECT empname, deptno FROM emp WHERE deptno > 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
3 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno >= 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| FORD    |     45 |
+---------+--------+
4 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno < 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
+---------+--------+
2 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno <= 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| BLAKE   |     30 |
| SCOTT   |     25 |
+---------+--------+
3 rows in set

Queries with logical conditions

Logical query operators AND and OR support queries with multiple conditions.

AND

Data that meets both conditions combined by AND is returned. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
query_condition AND query_condition;

Here is an example:

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno<=30 AND sal > 1000;

The return result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
+---------+--------+------+
2 rows in set

OR

Data that meets either of the conditions combined by OR is returned. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
query_condition OR query_condition;

Here is an example:

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno <= 30 OR sal > 1000;

The return result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

Fuzzy queries (LIKE)

You can use the LIKE predicate for fuzzy matching of strings. The syntax is as follows:

[NOT] LIKE pattern

The syntax means finding data that matches the corresponding column value with the pattern. The pattern can be a complete string or contain wildcards % and _, where:

The underscore (_) exactly matches any character in the value.
The percent sign (%) matches zero or multiple characters in the value, but cannot match NULL.

Note

In the Oracle compatible mode of OceanBase Cloud, the LIKE operator is replaced with = when the following conditions are met:

pattern does not contain the wildcard character % or _, and does not have an escape character.
The column type is not LOB. In the Oracle compatible mode, the LOB type does not support = comparison, so the conversion to = is not supported.
pattern is not of the fixed-length char or nchar type.

The following example queries employee names where the first four letters are ALLE and the last letter is any character.

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'ALLE_';

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

The following example queries employee names where the first letter is A.

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'A%';

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

Notice

If the database character set is ASCII, one Chinese character needs two underscores (_); if the database character set is GBK, one Chinese character needs only one underscore (_).

Range queries (BETWEEN AND)

The BETWEEN AND operator selects data between two values. These values can be numerals, literals, or dates. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
[NOT] BETWEEN min_const_value AND max_const_value;

Notice

The boundary values of a range query cannot be exchanged, as the values within the range must be greater than or equal to the left boundary and less than or equal to the right boundary.

Here is an example:

obclient> SELECT * FROM emp WHERE sal BETWEEN 2000 AND 2999;

The return result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

Queries with a specified set (IN)

The IN operator allows you to specify multiple values in a WHERE clause. The values can be treated as a set. The IN operator returns data, in the specified column, that matches any value in the set. The NOT IN operator returns data, in the specified column, that does not match any value in the set. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name [NOT] IN (const_value,const_value,const_value...) ;

Notice

The values in the [NOT] IN set must be of the same type or compatible types.
The values in the [NOT] IN set do not support wildcards.

Here is an example:

obclient> SELECT * FROM emp WHERE deptno IN (30,40,50,60);

The return result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

NULL value queries (IS NULL/IS NOT NULL)

Since you cannot obtain accurate results when you use comparison operators, LIKE, BETWEEN AND, IN, and NOT IN to query NULL values, we recommend that you use the dedicated query conditions IS NULL and IS NOT NULL for NULL value queries.

IS NULL

You can use the IS NULL condition to query the data where the value of the specified column is NULL. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NULL;

Here is an example:

obclient> SELECT * FROM emp WHERE comm IS NULL;

The return result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK   | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+---------+------+-----------+------+------+--------+------+
5 rows in set

IS NOT NULL

You can use the IS NOT NULL condition to query the data where the value of the specified column is NOT NULL. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NOT NULL;

Here is an example:

obclient> SELECT * FROM emp WHERE comm IS NOT NULL;

The return result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
1 row in set

GROUP BY queries

The ORDER BY clause sorts query results by one or multiple attribute columns in ascending or descending order. The query results are sorted in ascending order by default.

GROUP BY supports grouping by a single field or multiple fields. You can also use the WHERE clause to filter data before grouping, use the HAVING clause to filter data after grouping, and use the ORDER BY clause to sort data after grouping.

The following table lists frequently-used aggregate functions in GROUP BY queries.

Aggregate function	Description
MAX()	Queries the maximum value of the specified column.
MIN()	Queries the minimum value of the specified column.
COUNT()	Returns the number of rows in the query result.
SUM()	Returns the sum of the specified column.
AVG()	Returns the average value of the data in the specified column.

Examples

Create a table named fruit_order.

CREATE TABLE fruit_order(
  order_id Number(10,2),
  user_id Number(10,2),
  user_name VARCHAR2(16),
  fruit_price Number(10,2),
  order_year Date,
  PRIMARY KEY (order_id)
);

Insert test data.

INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
  (1,1011,'Zhang San',13.11,Date'2019-01-01'),
  (4,1011,'Zhang San',22.21,Date'2020-01-01'),
  (6,1011,'Zhang San',58.83,Date'2020-02-02'),
  (2,1022,'Li Si',23.34,Date'2019-02-02'),
  (3,1022,'Li Si',12.22,Date'2019-03-03'),
  (7,1022,'Li Si',14.66,Date'2021-03-03'),
  (8,1022,'Li Si',34.44,Date'2021-04-04'),
  (5,1033,'Wang Wu',51.55,Date'2020-05-05'),
  (9,1033,'Wang Wu',63.66,Date'2021-06-06');

GROUP BY query based on a single field

Query the number of orders placed by each customer and print the customer ID and the number of orders.

obclient [SYS]> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order GROUP BY user_id;

The return result is as follows:

+-------------+------------------+
| Customer ID | Number of orders |
+-------------+------------------+
|        1011 |                3 |
|        1022 |                4 |
|        1033 |                2 |
+-------------+------------------+
3 rows in set

GROUP BY query based on multiple fields

Query the number of orders placed by each customer each year and print the customer ID, the year of order, and the number of orders.

obclient [SYS]> SELECT user_id Customer ID, order_year Year of order, COUNT(order_id) Number of orders FROM fruit_order GROUP BY user_id,order_year;

The return result is as follows:

+-------------+---------------+---------+
| Customer ID | Year of order | Number of orders |
+-------------+---------------+---------+
|     1011    | 01-JAN-19     |       1 |
|     1022    | 02-FEB-19     |       1 |
|     1022    | 03-MAR-19     |       1 |
|     1011    | 01-JAN-20     |       1 |
|     1033    | 05-MAY-20     |       1 |
|     1011    | 02-FEB-20     |       1 |
|     1022    | 03-MAR-21     |       1 |
|     1022    | 04-APR-21     |       1 |
|     1033    | 06-JUN-21     |       1 |
+-------------+---------------+---------+
9 rows in set

Filtering before grouping

Query the number of orders placed by each customer in 2020 and print the customer ID and the number of orders.

obclient [SYS]> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order t WHERE t.order_year = '01-JAN-20' GROUP BY user_id;

The return result is as follows:

+-------------+------------------+
| Customer ID | Number of orders |
+-------------+------------------+
|        1011 |                1 |
+-------------+------------------+
1 row in set

Filtering after grouping

Query customers who placed one or more orders in 2019 and print the customer ID and the number of orders.

obclient [SYS]> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order t WHERE t.order_year = '01-JAN-19' GROUP BY user_id HAVING COUNT(order_id) >= 1;

The return result is as follows:

+-------------+------------------+
| Customer ID | Number of orders |
+-------------+------------------+
|        1011 |                1 |
+-------------+------------------+
1 row in set

Sorting after grouping

Query the maximum order amount of each customer and print the customer ID and maximum order amount in descending order of maximum order amount.

obclient [SYS]> SELECT user_id Customer ID, MAX(fruit_price) Maximum order amount FROM fruit_order t GROUP BY user_id ORDER BY Maximum order amount DESC;

The return result is as follows:

+-------------+----------------------+
| Customer ID | Maximum order amount |
+-------------+----------------------+
|        1033 |                63.66 |
|        1011 |                58.83 |
|        1022 |                34.44 |
+-------------+----------------------+
3 rows in set

CUBE grouping

You can use the GROUP BY CUBE clause to group records in the fruit_order table by user_id and sum the values in the fruit_price column for each group.

obclient [SYS]> SELECT user_id,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id);

The return result is as follows:

+---------+------------------+
| USER_ID | SUM(FRUIT_PRICE) |
+---------+------------------+
|    NULL |           294.02 |
|    1011 |            94.15 |
|    1022 |            84.66 |
|    1033 |           115.21 |
+---------+------------------+
4 rows in set

For more information about grouping summary, see SIMPLE SELECT.

ORDER BY queries

The ORDER BY clause sorts query results by one or multiple attribute columns in ascending or descending order. The query results are sorted in ascending order by default.

Query customers who placed one or more orders in 2019 and print the customer ID and the number of orders.

obclient [SYS]> SELECT user_id,USER_NAME,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id,USER_NAME);

The return result is as follows:

+---------+-----------+------------------+
| USER_ID | USER_NAME | SUM(FRUIT_PRICE) |
+---------+-----------+------------------+
|    NULL | NULL      |           294.02 |
|    NULL | Zhang San |            94.15 |
|    NULL | Li Si     |            84.66 |
|    NULL | Wang Wu   |           115.21 |
|    1011 | NULL      |            94.15 |
|    1022 | NULL      |            84.66 |
|    1033 | NULL      |           115.21 |
|    1011 | Zhang San |            94.15 |
|    1022 | Li Si     |            84.66 |
|    1033 | Wang Wu   |           115.21 |
+---------+-----------+------------------+
10 rows in set

Sorting by a single field

Display employee information in ascending order of `deptno`

obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
| BLAKE   |     30 |
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
6 rows in set

Display employee information in descending order of `deptno`

obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno DESC;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| FORD    |     45 |
| JONES   |     40 |
| ALLEN   |     35 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| SMITH   |     20 |
+---------+--------+
6 rows in set

Sorting by multiple fields

Display employee information in ascending order of deptno and descending order of sal.

obclient [SYS]> SELECT empname, deptno, sal FROM emp ORDER BY deptno ASC,sal DESC;

The return result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| SCOTT   |     25 | 3000 |
| BLAKE   |     30 | 2850 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

Sorting after the WHERE clause

Add ORDER BY after the WHERE clause for sorting. Here is an example:

obclient [SYS]> SELECT empname, deptno,sal, HIREDATE FROM emp WHERE sal>=1000 ORDER BY HIREDATE;

The return result is as follows:

+---------+--------+------+-----------+
| EMPNAME | DEPTNO | SAL  | HIREDATE  |
+---------+--------+------+-----------+
| ALLEN   |     35 | 1600 | 20-FEB-81 |
| JONES   |     40 | 2975 | 02-APR-81 |
| BLAKE   |     30 | 2850 | 01-MAY-81 |
| FORD    |     45 | 3000 | 05-DEC-81 |
| SCOTT   |     25 | 3000 | 15-JUL-87 |
+---------+--------+------+-----------+
5 rows in set

Sorting by using the NLSSORT function

You can use the NLSSORT function to sort by Chinese Pinyin or stroke number.

For more information about the NLSSORT function, see NLSSORT.

Sorting by Pinyin

obclient [SYS]> SELECT empname, deptno,sal FROM emp ORDER BY NLSSORT(empname,'NLS_SORT=UCA0900_SCHINESE_PINYIN');

The return result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| ALLEN   |     35 | 1600 |
| BLAKE   |     30 | 2850 |
| FORD    |     45 | 3000 |
| JONES   |     40 | 2975 |
| SCOTT   |     25 | 3000 |
| SMITH   |     20 |  800 |
+---------+--------+------+
6 rows in set

Use Row_Limiting_Clause in queries

You can use Row_Limiting_Clause to limit the number of rows returned for a SELECT query. This feature is often used in pagination.

Query data after the Nth row

Example 1

Query data after the fourth row.

obclient> SELECT * FROM tb OFFSET 4 ROW;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    5 | b    |  700 |
|    6 | a    |   80 |
+------+------+------+
2 rows in set

Example 2

Query data after the fifth row.

obclient> SELECT * FROM tb OFFSET 5 ROWS;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    6 | a    |   80 |
+------+------+------+
1 row in set

Query rows with the three smallest IDs

obclient> SELECT * FROM tb ORDER BY id FETCH FIRST 3 ROWS ONLY;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

Query rows with the two smallest IDs

obclient> SELECT id, name FROM tb ORDER BY id FETCH NEXT 2 ROWS ONLY;

The return result is as follows:

+------+------+
| ID   | NAME |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set

Query the first 30% of data by num

obclient> SELECT id, name,num FROM tb ORDER BY num
 FETCH FIRST 30 PERCENT ROWS ONLY;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

Query the first 30% of data with the minimum num values and all other data records same as the last data row obtained in the preceding example

obclient>  SELECT id, name,num FROM tb ORDER BY num FETCH FIRST 30 PERCENT ROWS WITH TIES;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

References

For more information about the SELECT statement, see SELECT.
For more information about subqueries, see Subqueries.
For more information about query statement optimization, see Overview of query rewrite.

Prerequisites

Syntax

Execution order of keywords in the SELECT statement

Notice

Create test tables and add test data to the tables

Basic queries

Query all columns

Specify a table alias in a query

Query specified columns

Set aliases for columns in a query

Specify to remove duplicate rows in a query

Limit the number of rows returned by using the ROWNUM clause

Query calculated values

FROM FUNCTION queries

Data filtering

Queries with comparison conditions

Equal to (=)

Not equal to (<> and !=)

Greater than (>) and less than (<)

Note

Queries with logical conditions

AND

OR

Fuzzy queries (LIKE)

Note

Notice

Range queries (BETWEEN AND)

Notice

Queries with a specified set (IN)

Notice

NULL value queries (IS NULL/IS NOT NULL)

IS NULL

IS NOT NULL

GROUP BY queries

Examples

GROUP BY query based on a single field

GROUP BY query based on multiple fields

Filtering before grouping

Filtering after grouping

Sorting after grouping

CUBE grouping

ORDER BY queries

Sorting by a single field

Display employee information in ascending order of deptno

Display employee information in descending order of deptno

Sorting by multiple fields

Sorting after the WHERE clause

Sorting by using the NLSSORT function

Sorting by Pinyin

Use Row_Limiting_Clause in queries

Query data after the Nth row

Example 1

Example 2

Query rows with the three smallest IDs

Query rows with the two smallest IDs

Query the first 30% of data by num

Query the first 30% of data with the minimum num values and all other data records same as the last data row obtained in the preceding example

References

Related documents

Limit the number of rows returned by using the `ROWNUM` clause

Display employee information in ascending order of `deptno`

Display employee information in descending order of `deptno`