Single-table queries

2024-03-05 01:54:26  Updated

A single-table SQL query is performed only in one table.

Syntax

The syntax of a single-table query 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]
              [ LIMIT limit_clause ]

column_list:
 column_name[,column_name...]

When the keywords WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT are used together, there are specific restrictions on the order. The execution order of these keywords is as follows:

  1. Execute FROM to find the required table.

  2. Execute WHERE to specify the constraint conditions.

  3. Execute GROUP BY to group each retrieved record. If GROUP BY is not executed, all records are considered a group.

  4. Execute HAVING to filter the grouped results.

  5. Execute SELECT.

  6. Execute DISTINCT to remove duplicate rows.

  7. Execute ORDER BY to sort the results in ascending or descending order based on the conditions.

  8. Execute ROWNUM to limit the number of records displayed per page.

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.

For more information about how to use query statements, see SELECT.

SELECT queries

Assume that there are two tables emp and dept with the following data:

obclient [SYS]> SELECT * FROM dept;
+--------+----------------+-----------+
| DEPTNO | DNAME          | LOCATION  |
+--------+----------------+-----------+
|     20 | Finance        | beijing   |
|     35 | Administration | hangzhou  |
|     40 | Development    | xian      |
|     30 | Workshop       | guangzhou |
|     25 | Legal affairs  | shanghai  |
|     45 | Office         | suzhou    |
+--------+----------------+-----------+
6 rows in set

obclient [SYS]> SELECT * FROM emp;
+-------+---------+----------+------+-----------+------+------+--------+------+
| 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 all columns

An asterisk (*) indicates to return all columns in a table. For example:

obclient> SELECT * FROM emp;
+-------+---------+----------+------+-----------+------+------+--------+------+
| 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 preceding sample code is equivalent to:

obclient> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;
+---------+-------+----------+------+-----------+------+------+--------+
| 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 while setting the table alias to t.

obclient [SYS]> SELECT t.* FROM (emp) t;
+-------+---------+----------+------+-----------+------+------+--------+------+
| 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> SELECT empname,deptno FROM emp;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
6 rows in set

  • You can set aliases for columns in a query. For example:

    obclient> SELECT empname AS Employee name, deptno AS Department No. FROM emp;
+---------------+----------------+
| Employee name | Department No. |
+---------------+----------------+
| SMITH         |             20 |
| ALLEN         |             35 |
| JONES         |             40 |
| BLAKE         |             30 |
| SCOTT         |             25 |
| FORD          |             45 |
+---------------+----------------+
6 rows in set

  • You can remove duplicate rows in a query. For example:

    obclient> SELECT age FROM emp;
+------+
| AGE  |
+------+
|   22 |
|   22 |
|   22 |
|   33 |
|   33 |
|   22 |
+------+
6 rows in set

obclient> SELECT DISTINCT age FROM emp;
+------+
| AGE  |
+------+
|   22 |
|   33 |
+------+
2 rows in set

  • You can use the ROWNUM clause to limit the number of rows returned per page. This feature is often used in pagination. For example:

    obclient> SELECT empname, deptno FROM emp WHERE ROWNUM<=3;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
+---------+--------+
3 rows in set

Query calculated values

obclient> SELECT empname, sal-100,job FROM emp;
+---------+---------+----------+
| 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 values in the job column to lowercase. For more information, see "Use operators and functions in queries".

obclient> SELECT empname, sal-100, LOWER(job) FROM emp;
+---------+---------+------------+
| 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 a table.

  • The function must return the same number and type of columns as those required by the called party.

  • The function must return the same or more rows than those required by the called party.

  • 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 a function and CREATE FUNCTION.

Here is an example:

  1. 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

  2. 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

  3. 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 ;

  4. 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

Conditional queries

To query data that satisfies specified conditions, add a WHERE clause to the SELECT statement.

Syntax

When a conditional query is executed, the records that meet the conditions specified by the WHERE clause following the FROM clause are returned, and then the columns specified by the SELECT clause are selected.

The syntax for conditional queries is as follows:

SELECT select_list FROM table_list
WHERE query_condition;

The WHERE keyword can be followed by one or more conditions. These conditions are used to filter the data, and only the data that satisfies the WHERE conditions will be returned.

General query conditions

The commonly used query conditions in the WHERE clause are shown in the table below.

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

Comparison operators include equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (!= and <>).

Equal to (=)

Query data from the specified column that is equal to the target value. If the value is of string type, it needs to be enclosed in single or double quotes.

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

Here is an example:

obclient [SYS]> SELECT empname, deptno FROM emp WHERE deptno = 30;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| BLAKE   |     30 |
+---------+--------+
1 row in set

obclient [SYS]> SELECT empname, deptno FROM emp WHERE deptno = '30';
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| BLAKE   |     30 |
+---------+--------+
1 row in set

obclient [SYS]> SELECT empname, deptno FROM emp WHERE empname = 'ALLEN';
+---------+--------+
| 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;
+---------+--------+
| 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;
+---------+--------+
| 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;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
3 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno >= 30;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| FORD    |     45 |
+---------+--------+
4 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno < 30;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
+---------+--------+
2 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno <= 30;
+---------+--------+
| 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

Return data that satisfies both conditions with AND. 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;
+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
+---------+--------+------+
2 rows in set

OR

Return data that satisfies either one of the conditions. 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;
+---------+--------+------+
| 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_';
+---------+--------+
| 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%';
+---------+--------+
| 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;
+-------+---------+---------+------+-----------+------+------+--------+------+
| 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 is used to specify multiple values as a set in a WHERE clause. It returns data from the specified column that matches any value in the set. On the other hand, the NOT IN operator returns data from 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);
+-------+---------+---------+------+-----------+------+------+--------+------+
| 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

Query data where the specified column has a NULL value. 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;
+-------+---------+---------+------+-----------+------+------+--------+------+
| 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

Query data where the specified column has a non-NULL value. 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;
+-------+---------+----------+------+-----------+------+------+--------+------+
| 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.

Syntax

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

SELECT select_list FROM table_list
  [WHERE query_condition]
  GROUP BY group_by_expression
  [HAVING group_condition];

select_list:
  column_name, group_function,...

where

  • group_function specifies the aggregate function.

  • group_by_expression specifies the group expression. Separate multiple expressions by commas (,).

  • group_condition specifies to filter data after it is grouped.

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 and insert proper data.

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 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 queries 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> SELECT user_id Customer ID, COUNT(order_id) Number of orders FROM fruit_order GROUP BY user_id;
+-------------+------------------+
| Customer ID | Number of orders |
+-------------+------------------+
|        1011 |                3 |
|        1022 |                4 |
|        1033 |                2 |
+-------------+------------------+
3 rows in set

GROUP BY queries 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> 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;
+-------------+-------------------------+------------------+
| Customer ID | Year of order placement | Number of orders |
+-------------+-------------------------+------------------+
|        1011 | 01-JAN-19               |                1 |
|        1011 | 01-JAN-20               |                1 |
|        1011 | 02-FEB-20               |                1 |
|        1022 | 02-FEB-19               |                1 |
|        1022 | 03-MAR-19               |                1 |
|        1022 | 03-MAR-21               |                1 |
|        1022 | 04-APR-21               |                1 |
|        1033 | 05-MAY-20               |                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> 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;
+-------------+------------------+
| 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 ID and the number of orders.

obclient> 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;
+-------------+------------------+
| 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 ID and maximum order amount in descending order of maximum order amount.

obclient> 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;
+-------------+----------------------+
| Customer ID | Maximum order amount |
+-------------+----------------------+
|        1033 |                63.66 |
|        1011 |                58.83 |
|        1022 |                34.44 |
+-------------+----------------------+
3 rows in set

Sum data after 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.

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 is used to sort the query results in ascending (ASC) or descending (DESC) order based on one or more attribute columns. The default order is ascending.

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

obclient> SELECT user_id,USER_NAME,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id,USER_NAME);
+-------------+------------------+
| Customer ID | Number of orders |
+-------------+------------------+
|        1011 |                1 |
+-------------+------------------+
1 row in set

Single-field sorting

  • Display employee names by deptno in ascending order.

    obclient> SELECT empname, deptno FROM emp ORDER BY deptno;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
| BLAKE   |     30 |
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
6 rows in set

  • Display employee names by deptno in descending order.

    obclient> SELECT empname, deptno FROM emp ORDER BY deptno DESC;
+---------+--------+
| 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> SELECT empname, deptno, sal FROM emp ORDER BY deptno ASC,sal DESC;
+---------+--------+------+
| 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. For example:

obclient> SELECT empname, deptno,sal, HIREDATE FROM emp WHERE sal>=1000 ORDER BY HIREDATE;
+---------+--------+------+-----------+
| 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 count.

For more information about the NLSSORT function, see NLSSORT.

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;
+------+------+------+
| 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;
+------+------+------+
| 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;
+------+------+------+
| 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;
+------+------+
| 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;
+------+------+------+
| 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;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

References

Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartner

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite PaperTrust Center

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us