Single-table queries|V4.3.3| docs|Distributed Database

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

Prerequisites

You have connected to an Oracle tenant of OceanBase Database. For more information, see Connection methods.
You have the SELECT privilege. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges.

Syntax

You can use the SELECT statement to query data.

The general structure 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...]

The parameters are described as follows:

Parameter	Description
select_list	The list of columns to be retrieved, including column names, expressions, and aggregate functions. Multiple columns can be separated by commas.
table_name	The name of the table from which to retrieve data.
WHERE query_condition	(Optional) Specifies the retrieve condition. Only rows that meet the condition will be returned.
GROUP BY group_by_condition	(Optional) Groups the results by the specified column. This parameter is typically used with aggregate functions.
HAVING group_condition	(Optional) Filters the grouped result set. Only groups that meet the condition are returned.
ORDER BY column_list	(Optional) Sorts the result set. You can specify one or multiple columns for sorting.
ASC \| DESC	(Optional) Specifies the order of sorting. ASC indicates ascending order (default), and DESC indicates descending order.
LIMIT limit_clause	(Optional) Limits the number of rows returned in a query to implement the paging functionality. With this parameter, you can specify an offset, as well as the number of rows or a percentage of rows to return. This parameter can be combined with the `ORDER BY` clause to ensure a sorting order and obtain consistent results.
column_list	Specifies the columns to be retrieved. The value of this parameter can be a single column or multiple columns separated by commas.
column_name	The name of the column to be retrieved.

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 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 or aggregation, while HAVING filters data after grouping and returns the entire query results.

Create a test table and add test data to the table

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 6 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 6 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

The * symbol indicates that all fields in the table are returned. Here is an example:

obclient [SYS]> SELECT * FROM emp;

The 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

Here is another example:

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

The 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

You can query data from table emp and specify the table alias as t.

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

The 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 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 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 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 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 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 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 mode of OceanBase Database, 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, observe the following requirements:

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 user-defined 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 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 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 commonly used query conditions specified by the WHERE clause.

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

Queries with comparison operators

Equal to (=)

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

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

The result is as follows:

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

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

The result is as follows:

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

Not equal to (<> and !=)

Not equal to operators include <> 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 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 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 is an example:

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

The 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 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 result is as follows:

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

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

The 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 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 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)

The predicate LIKE can be used for string matching. 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. The pattern % cannot match NULL.
Note

In the Oracle mode of OceanBase Database, 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 Oracle 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 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 result is as follows:

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

Notice

If the database character set uses ASCII, one Chinese character requires two underscores (_); if the database character set uses GBK, one Chinese character requires only one underscores (_).

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

Do not swap the two boundary values of a range query. The left boundary value should be greater than or equal to the starting point, and the right boundary value should be less than or equal to the ending point.

Here is an example:

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

The 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 value in the [NOT] IN set must be of the same type or compatible with each other.
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 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

IS NULL/IS NOT NULL

Due to the inaccurate results obtained when using comparison operators, LIKE, BETWEEN AND, IN, and NOT IN to query for NULL values, we recommend that you use the dedicated query statements IS NULL and IS NOT NULL for NULL value queries.

IS NULL

IS NULL queries NULL values from the specified column. 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 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

IS NOT NULL queries non-NULL values from the specified column. 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 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 commonly 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,'Tom',13.11,Date'2019-01-01'),
  (4,1011,'Tom',22.21,Date'2020-01-01'),
  (6,1011,'Tom',58.83,Date'2020-02-02'),
  (2,1022,'Jack',23.34,Date'2019-02-02'),
  (3,1022,'Jack',12.22,Date'2019-03-03'),
  (7,1022,'Jack',14.66,Date'2021-03-03'),
  (8,1022,'Jack',34.44,Date'2021-04-04'),
  (5,1033,'Alice',51.55,Date'2020-05-05'),
  (9,1033,'Alice',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 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 placement, and the number of orders.

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

The result is as follows:

+----------+--------------+--------------+
| Customer ID   | Year of order placement     | 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

Filter data 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 result is as follows:

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

Filter data after grouping

Query customers who placed one or more orders in 2019 and print the customer IDs and the numbers 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 result is as follows:

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

Sort data after grouping

Query the maximum order amount of each customer and print the customer IDs and maximum order amounts 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 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 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, 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 IDs 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 result is as follows:

+---------+-----------+------------------+
| USER_ID | USER_NAME | SUM(FRUIT_PRICE) |
+---------+-----------+------------------+
|    NULL | NULL      |           294.02 |
|    NULL | Tom      |            94.15 |
|    NULL | Jack      |            84.66 |
|    NULL | Alice      |           115.21 |
|    1011 | NULL      |            94.15 |
|    1022 | NULL      |            84.66 |
|    1033 | NULL      |           115.21 |
|    1011 | Tom      |            94.15 |
|    1022 | Jack      |            84.66 |
|    1033 | Alice      |           115.21 |
+---------+-----------+------------------+
10 rows in set

Single-field sorting

Display employee names in ascending order of `deptno`

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

The result is as follows:

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

Display employee names in descending order of `deptno`

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

The result is as follows:

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

Multiple-field sorting

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

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

The 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 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 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 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 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 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 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 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 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 optimization, see Overview.

Single-table queries

Prerequisites

Syntax

Execution order of keywords in the SELECT statement

Notice

Create a test table and add test data to the table

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 operators

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

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

Filter data before grouping

Filter data after grouping

Sort data after grouping

CUBE grouping

ORDER BY queries

Single-field sorting

Display employee names in ascending order of deptno

Display employee names in descending order of deptno

Multiple-field sorting

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

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

Display employee names in ascending order of `deptno`

Display employee names in descending order of `deptno`