Single-table queries

This topic describes how to perform single-table queries in OceanBase Database by using SQL statements.

Prerequisites

• You have connected to a MySQL tenant of OceanBase Database.
• 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 them. For more information, see Grant direct privileges.

Syntax

You can execute the SELECT statement to query data.

The general structure of a SELECT statement for single-table queries is as follows:

SELECT [ALL | DISTINCT | UNIQUE | SQL_CALC_FOUND_ROWS] select_list
FROM table_name
[ WHERE query_condition ]
[ GROUP BY group_by_condition ]
[ HAVING group_condition ]
[ ORDER BY column_list ][ASC | DESC]
[ LIMIT limit_clause ]

column_list:
 column_name[,column_name...]

The parameters are described as follows:

Execution order of keywords in the SELECT statement

When the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT keywords are used together, the execution order is as follows:

1. The FROM clause is executed to find the tables.

2. The WHERE clause is executed to specify conditions.

3. The GROUP BY clause is executed to group or aggregate records. If GROUP BY is not executed, all records are considered a group.

4. The HAVING clause is executed to filter the grouped results.

5. The SELECT clause is executed.

6. The DISTINCT clause is executed to remove duplicate rows.

7. The ORDER BY clause is executed to sort the results in ascending or descending order.

8. The LIMIT clause is executed to limit the number of rows in the results.

Create test tables and add test data

1. Create a table named student.

   CREATE TABLE student (
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(20) NOT NULL,
     gender TINYINT NOT NULL,
     age INT NOT NULL,
     score FLOAT NOT NULL,
     enrollment_date DATE NOT NULL,
     notes VARCHAR(50)
   );
   

2. Insert 10 records into the student table.

   INSERT INTO student (name, gender, age, score, enrollment_date, notes)
     VALUES ('Emma', 0, 20, 85.0, '2021-09-01',NULL),
     ('William', 1, 21, 90.5, '2021-09-02','B'),
     ('Olivia', 0, 19, 95.5, '2021-09-03','A'),
     ('James', 1, 20, 87.5, '2021-09-03',NULL),
     ('Sophia', 0, 20, 91.5, '2021-09-05','B'),
     ('Benjamin', 1, 21, 96.5, '2021-09-01','A'),
     ('Ava', 0, 22, 89.5, '2021-09-06',NULL),
     ('Michael', 1, 18, 93.5, '2021-09-08','B'),
     ('Charlotte', 1, 19, 88.0, '2021-09-06',NULL),
     ('Ethan', 1, 20, 92.0, '2021-09-01','B');
   

3. Create a table named fruit_order.

   CREATE TABLE fruit_order(
     order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Order ID',
     user_id BIGINT NOT NULL COMMENT 'Customer ID',
     user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Customer name',
     fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Order amount',
     order_year SMALLINT NOT NULL COMMENT 'Year when the order was placed'
   ) COMMENT 'Order table';
   

4. Insert 10 records into the fruit_order table.

   INSERT INTO fruit_order(user_id, user_name,fruit_price,order_year)
     VALUES (1011,'A1',13.11,'2019'),
     (1011,'A1',22.21,'2020'),
     (1011,'A1',58.83,'2020'),
     (1022,'B2',23.34,'2019'),
     (1022,'B2',12.22,'2019'),
     (1022,'B2',14.66,'2021'),
     (1022,'B2',34.44,'2021'),
     (1033,'C3',51.55,'2020'),
     (1033,'C3',63.66,'2021'),
     (1034,'D4',53.62,'2021');
   

Basic queries

When you use SELECT, we recommend that you use meaningful column aliases and properly organize the columns to improve the readability and organization of the result set, making it easier to understand the query results.

Query all columns

• You can execute the SELECT * FROM student; statement to query all student information.

• You can also execute the SELECT id,name,gender,age,score,enrollment_date FROM student; statement to query all student information.

Example 1: Execute the following SQL statement to query data of all rows in the student table.

SELECT id, name, gender, age, score, enrollment_date, notes
FROM student;

or

SELECT * FROM student;

The return result is as follows:

+----+-----------+--------+-----+-------+-----------------+-------+
| id | name      | gender | age | score | enrollment_date | notes |
+----+-----------+--------+-----+-------+-----------------+-------+
|  1 | Emma      |      0 |  20 |    85 | 2021-09-01      | NULL  |
|  2 | William   |      1 |  21 |  90.5 | 2021-09-02      | B     |
|  3 | Olivia    |      0 |  19 |  95.5 | 2021-09-03      | A     |
|  4 | James     |      1 |  20 |  87.5 | 2021-09-03      | NULL  |
|  5 | Sophia    |      0 |  20 |  91.5 | 2021-09-05      | B     |
|  6 | Benjamin  |      1 |  21 |  96.5 | 2021-09-01      | A     |
|  7 | Ava       |      0 |  22 |  89.5 | 2021-09-06      | NULL  |
|  8 | Michael   |      1 |  18 |  93.5 | 2021-09-08      | B     |
|  9 | Charlotte |      1 |  19 |    88 | 2021-09-06      | NULL  |
| 10 | Ethan     |      1 |  20 |    92 | 2021-09-01      | B     |
+----+-----------+--------+-----+-------+-----------------+-------+
10 rows in set

Query specified columns

You can search for data in a specified column by column name in a table.

Example 2: Execute the following SQL statement to query data of all rows in the student table and return only the id and name columns in the result set.

SELECT id, name
FROM student;

The return result is as follows:

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Emma      |
|  2 | William   |
|  3 | Olivia    |
|  4 | James     |
|  5 | Sophia    |
|  6 | Benjamin  |
|  7 | Ava       |
|  8 | Michael   |
|  9 | Charlotte |
| 10 | Ethan     |
+----+-----------+
10 rows in set

Query calculated values and specify column aliases

You can calculate the data of specified columns in a query.

Example 3: Select data from the id, name, age, and age+5 columns of the student table, and specify the alias age_plus_5 for the age+5 column that stores the result of calculation.

SELECT id, name, age, age+5 AS age_plus_5
FROM student;

The return result is as follows:

+----+-----------+-----+------------+
| id | name      | age | age_plus_5 |
+----+-----------+-----+------------+
|  1 | Emma      |  20 |         25 |
|  2 | William   |  21 |         26 |
|  3 | Olivia    |  19 |         24 |
|  4 | James     |  20 |         25 |
|  5 | Sophia    |  20 |         25 |
|  6 | Benjamin  |  21 |         26 |
|  7 | Ava       |  22 |         27 |
|  8 | Michael   |  18 |         23 |
|  9 | Charlotte |  19 |         24 |
| 10 | Ethan     |  20 |         25 |
+----+-----------+-----+------------+
10 rows in set

Data filtering

To query data that meets specific conditions, you can add a WHERE clause to the SELECT query statement for data filtering. The WHERE clause can include one or more conditions, which are used to filter the data. Only the data that satisfies the WHERE conditions will be returned. Based on specific requirements, you can flexibly use query conditions to filter and retrieve target data.

Make sure that the conditions are correct and that appropriate operators are used when you use the WHERE clause.

The following table describes the commonly used query conditions in the WHERE clause.

For more information about operators in query conditions, see Comparison operators.

Queries with comparison operators

Equal to

The equal to (=) operator queries data that is equal to the target value in the specified column. If the value is of the string type, it must be enclosed in single or double quotation marks.

Example 4: The following SQL statement queries all rows in the student table where the value in the gender column is 1, and returns the data in the id, name, and gender columns of these rows.

SELECT id, name, gender
FROM student
WHERE gender = 1;

The return result is as follows:

+----+-----------+--------+
| id | name      | gender |
+----+-----------+--------+
|  2 | William   |      1 |
|  4 | James     |      1 |
|  6 | Benjamin  |      1 |
|  8 | Michael   |      1 |
|  9 | Charlotte |      1 |
| 10 | Ethan     |      1 |
+----+-----------+--------+
6 rows in set

Not equal to

This operator includes two expressions: <> and !=.

Example 5: The following SQL statement queries all rows in the student table where the value in the gender column is not 1, and returns the data in the id, name, and gender columns of these rows.

SELECT id, name, gender
FROM student
WHERE gender <> 1;

The return result is as follows:

+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | Emma   |      0 |
|  3 | Olivia |      0 |
|  5 | Sophia |      0 |
|  7 | Ava    |      0 |
+----+--------+--------+
4 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.

Example 6: The following SQL statement queries all rows in the student table where the value in the score column is less than 90, and returns the data in the id, name, and score columns of these rows.

SELECT id, name, score
FROM student
WHERE score < 90;

The return result is as follows:

+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Emma      |    85 |
|  4 | James     |  87.5 |
|  7 | Ava       |  89.5 |
|  9 | Charlotte |    88 |
+----+-----------+-------+
4 rows in set

Queries with logical conditions

Logical operators AND and OR can be used to perform queries with multiple conditions.

AND

The AND operator combines multiple conditions. Only data that meets all the conditions will be returned.

Example 7: The following SQL statement queries all rows in the student table where the value in the gender column is 1 and the value in the score column is less than or equal to 90, and returns the data in the id, name, gender, and score columns of these rows.

SELECT id, name, gender, score
FROM student
WHERE gender = 1 AND score <= 90;

The return result is as follows:

+----+-----------+--------+-------+
| id | name      | gender | score |
+----+-----------+--------+-------+
|  4 | James     |      1 |  87.5 |
|  9 | Charlotte |      1 |    88 |
+----+-----------+--------+-------+
2 rows in set

OR

The OR operator is used to combine multiple conditions. Data that meets any of the conditions will be returned.

Example 8: The following SQL statement queries all rows in the student table where the value in the gender column is 1 or the value in the score column is less than 90, and returns the data in the id, name, gender, and score columns of these rows.

SELECT id, name, gender, score
FROM student
WHERE gender = 1 OR score < 90;

The return result is as follows:

+----+-----------+--------+-------+
| id | name      | gender | score |
+----+-----------+--------+-------+
|  1 | Emma      |      0 |    85 |
|  2 | William   |      1 |  90.5 |
|  4 | James     |      1 |  87.5 |
|  6 | Benjamin  |      1 |  96.5 |
|  7 | Ava       |      0 |  89.5 |
|  8 | Michael   |      1 |  93.5 |
|  9 | Charlotte |      1 |    88 |
| 10 | Ethan     |      1 |    92 |
+----+-----------+--------+-------+
8 rows in set

Fuzzy queries

The LIKE predicate can be used for fuzzy matching of strings.

Its syntax is used to find data where the value of the corresponding column matches the pattern. The pattern can be a complete string or include the wildcard characters %and _. Specifically:

• The underscore (_) matches any single character.

• The percent sign (%) matches zero or more characters in a value. % does not match NULL.

Example 9: The following SQL statement queries all rows in the student table where the value in the name column contains am, and returns the data in the id and name columns of these rows.

SELECT id, name
FROM student
WHERE name LIKE '%am%';

The return result is as follows:

+----+----------+
| id | name     |
+----+----------+
|  2 | William  |
|  4 | James    |
|  6 | Benjamin |
+----+----------+
3 rows in set

Range queries

The BETWEEN AND operator selects data between two values. These values can be numerals, literals, or dates.

Example 10: The following SQL statement queries all rows in the student table where the value in the score column is between 85 and 90, and returns the data in the id, name, and score columns of these rows.

SELECT id, name, score
FROM student
WHERE score BETWEEN 85 AND 90;

The return result is as follows:

+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Emma      |    85 |
|  4 | James     |  87.5 |
|  7 | Ava       |  89.5 |
|  9 | Charlotte |    88 |
+----+-----------+-------+
4 rows in set

Queries with a specified set

The IN operator specifies multiple values, referred to as a set, in the WHERE clause. IN means that data in the specified column will be returned as long as it matches any value in the set. NOT IN means that only data that does not match any value in the set will be returned.

Example 11: The following SQL statement queries all rows in the student table where the value in the id column is in the set (1, 3, 5, 7), and returns the data in the id and name columns of these rows.

SELECT id, name
FROM student
WHERE id IN (1,3,5,7);

The return result is as follows:

+----+--------+
| id | name   |
+----+--------+
|  1 | Emma   |
|  3 | Olivia |
|  5 | Sophia |
|  7 | Ava    |
+----+--------+
4 rows in set

Queries for NULL values

Since comparison operators, LIKE, BETWEEN AND, IN, and NOT IN may not return accurate results when dealing with NULL values, it is recommended to use the dedicated query statements IS NULL and IS NOT NULL for handling NULL. Alternatively, the safe equal operator (<=>) can be used, as it works with both regular values and NULL.

IS NULL

The IS NULL condition is used to query data whose value in the specified column is NULL.

Example 12: Execute the following SQL statement to query all rows whose notes value is NULL in the student table and return the id, name, score, and notes values of these rows.

SELECT id, name, score, notes
FROM student
WHERE notes IS NULL;

The return result is as follows:

+----+-----------+-------+-------+
| id | name      | score | notes |
+----+-----------+-------+-------+
|  1 | Emma      |    85 | NULL  |
|  4 | James     |  87.5 | NULL  |
|  7 | Ava       |  89.5 | NULL  |
|  9 | Charlotte |    88 | NULL  |
+----+-----------+-------+-------+
4 rows in set

IS NOT NULL

The IS NOT NULL condition is used to query data whose value in the specified column is not NULL.

Example 13: Execute the following SQL statement to query all rows whose notes value is not NULL in the student table and return the id, name, score, and notes values of these rows.

SELECT id, name, score, notes
FROM student
WHERE notes IS NOT NULL;

The return result is as follows:

+----+----------+-------+-------+
| id | name     | score | notes |
+----+----------+-------+-------+
|  2 | William  |  90.5 | B     |
|  3 | Olivia   |  95.5 | A     |
|  5 | Sophia   |  91.5 | B     |
|  6 | Benjamin |  96.5 | A     |
|  8 | Michael  |  93.5 | B     |
| 10 | Ethan    |    92 | B     |
+----+----------+-------+-------+
6 rows in set

GROUP BY queries

In SQL queries, the GROUP BY clause is used to group the query results. GROUP BY supports both single-field grouping and multi-field grouping. Before grouping, the WHERE clause can be used to filter data. After grouping, the HAVING clause can be used to filter the grouped data, and the ORDER BY clause can be used to sort the grouped data.

Considerations:

• When you use the GROUP BY clause, the columns in the SELECT clause must be either the columns in the GROUP BY clause or aggregate functions.
• When you use the HAVING clause, the HAVING condition is used to filter the grouped results, not the original data.

GROUP BY based on a single field

Example 14: Query the number of orders placed by each customer in the fruit_order table and return the user_id and COUNT(order_id).

SELECT user_id, COUNT(order_id)
FROM fruit_order
GROUP BY user_id;

The return result is as follows:

+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
|    1011 |               3 |
|    1022 |               4 |
|    1033 |               2 |
|    1034 |               1 |
+---------+-----------------+
4 rows in set

GROUP BY based on multiple fields

Example 15: Query the number of orders placed by each customer each year in the fruit_order table, and return the data of the user_id, order_year, and COUNT(order_id) columns.

SELECT user_id, order_year, COUNT(order_id)
FROM fruit_order
GROUP BY user_id,order_year;

The return result is as follows:

+---------+------------+-----------------+
| user_id | order_year | COUNT(order_id) |
+---------+------------+-----------------+
|    1011 |       2019 |               1 |
|    1011 |       2020 |               2 |
|    1022 |       2019 |               2 |
|    1022 |       2021 |               2 |
|    1033 |       2020 |               1 |
|    1033 |       2021 |               1 |
|    1034 |       2021 |               1 |
+---------+------------+-----------------+
7 rows in set

Data filtering before grouping

Example 16: Query the number of orders placed by each customer in 2020, and return the data of the user_id and COUNT(order_id) columns.

SELECT user_id, COUNT(order_id)
FROM fruit_order t
WHERE t.order_year = 2020
GROUP BY user_id;

The return result is as follows:

+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
|    1011 |               2 |
|    1033 |               1 |
+---------+-----------------+
2 rows in set

Data filtering after grouping

Example 17: Query the customers who placed more than one order in 2019 and return the data of user_id and COUNT(order_id).

SELECT user_id, COUNT(order_id)
FROM fruit_order t
WHERE t.order_year = 2019
GROUP BY user_id
HAVING COUNT(order_id) >= 2;

The return result is as follows:

+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
|    1022 |               2 |
+---------+-----------------+
1 row in set

Data sorting after grouping

Example 18: Query the maximum order amount of each customer and return the data of user_id and MAX(fruit_price) sorted by maximum order amount in descending order.

SELECT user_id, MAX(fruit_price)
FROM fruit_order t
GROUP BY user_id
ORDER BY MAX(fruit_price) DESC;

The return result is as follows:

+---------+------------------+
| user_id | MAX(fruit_price) |
+---------+------------------+
|    1033 |            63.66 |
|    1011 |            58.83 |
|    1034 |            53.62 |
|    1022 |            34.44 |
+---------+------------------+
4 rows in set

Aggregate queries

An aggregate query is a type of query used to perform operations on data and return summarized results. It can calculate statistics, counts, sums, averages, maximums, minimums, and other aggregate values for a dataset. Aggregate queries are typically used with the GROUP BY clause, which groups data based on specified columns. Aggregate functions are then applied to each group, producing a summarized result set.

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

For more information about aggregate functions, see Use aggregate functions in queries.

Data sorting

Data sorting is an operation that arranges query results based on specified columns or expressions, either in ascending order (ASC) or descending order (DESC). In SQL queries, the ORDER BY clause is used to specify how the data should be sorted. The ORDER BY clause supports single-column sorting, multi-column sorting, sorting by aliases, and sorting by functions, with multiple fields separated by commas. When performing a sorting query, if the ASC or DESC keyword is not specified, the query results will be sorted in ascending order by default.

Using the ORDER BY clause to sort result sets is a resource-intensive operation, especially for large datasets. When necessary, it is recommended to use indexes to optimize sorting operations. Ensure that the correct columns and sorting order are specified.

Sorting by single field

Example 19: Display the student information in the student table sorted by the score column in ascending order.

SELECT id, name, score
FROM student
ORDER BY score;

The return result is as follows:

+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Emma      |    85 |
|  4 | James     |  87.5 |
|  9 | Charlotte |    88 |
|  7 | Ava       |  89.5 |
|  2 | William   |  90.5 |
|  5 | Sophia    |  91.5 |
| 10 | Ethan     |    92 |
|  8 | Michael   |  93.5 |
|  3 | Olivia    |  95.5 |
|  6 | Benjamin  |  96.5 |
+----+-----------+-------+
10 rows in set

Example 20: Display the student information in the student table sorted by the score column in descending order.

SELECT id, name, score
FROM student
ORDER BY score DESC;

The return result is as follows:

+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  6 | Benjamin  |  96.5 |
|  3 | Olivia    |  95.5 |
|  8 | Michael   |  93.5 |
| 10 | Ethan     |    92 |
|  5 | Sophia    |  91.5 |
|  2 | William   |  90.5 |
|  7 | Ava       |  89.5 |
|  9 | Charlotte |    88 |
|  4 | James     |  87.5 |
|  1 | Emma      |    85 |
+----+-----------+-------+
10 rows in set

Sorting by multiple fields

Example 21: Display the student information in the student table sorted by the enrollment_date column in descending order and the score column in ascending order.

SELECT id, name, score, enrollment_date
FROM student
ORDER BY enrollment_date DESC,score ASC;

The return result is as follows:

+----+-----------+-------+-----------------+
| id | name      | score | enrollment_date |
+----+-----------+-------+-----------------+
|  8 | Michael   |  93.5 | 2021-09-08      |
|  9 | Charlotte |    88 | 2021-09-06      |
|  7 | Ava       |  89.5 | 2021-09-06      |
|  5 | Sophia    |  91.5 | 2021-09-05      |
|  4 | James     |  87.5 | 2021-09-03      |
|  3 | Olivia    |  95.5 | 2021-09-03      |
|  2 | William   |  90.5 | 2021-09-02      |
|  1 | Emma      |    85 | 2021-09-01      |
| 10 | Ethan     |    92 | 2021-09-01      |
|  6 | Benjamin  |  96.5 | 2021-09-01      |
+----+-----------+-------+-----------------+
10 rows in set

Sorting by functions in the ORDER BY clause

In the ORDER BY clause, you can use functions to sort query results. These functions can be applied to the columns or expressions being sorted, allowing for more advanced sorting operations. Example 22: Display the student information in the student table sorted by score in ascending order and enrollment_date in descending order.

SELECT id, name, score, enrollment_date
FROM student
ORDER BY DAY(enrollment_date) DESC,score ASC;

The return result is as follows:

+----+-----------+-------+-----------------+
| id | name      | score | enrollment_date |
+----+-----------+-------+-----------------+
|  8 | Michael   |  93.5 | 2021-09-08      |
|  9 | Charlotte |    88 | 2021-09-06      |
|  7 | Ava       |  89.5 | 2021-09-06      |
|  5 | Sophia    |  91.5 | 2021-09-05      |
|  4 | James     |  87.5 | 2021-09-03      |
|  3 | Olivia    |  95.5 | 2021-09-03      |
|  2 | William   |  90.5 | 2021-09-02      |
|  1 | Emma      |    85 | 2021-09-01      |
| 10 | Ethan     |    92 | 2021-09-01      |
|  6 | Benjamin  |  96.5 | 2021-09-01      |
+----+-----------+-------+-----------------+
10 rows in set

Sorting data after filtering

The WHERE clause can be used to filter data before sorting.

Example 23: Query the student table for students with a score greater than 85, and display the student information in ascending order of DAY(enrollment_date).

SELECT id, name, score, DAY(enrollment_date)
FROM student
WHERE score > 85
ORDER BY DAY(enrollment_date) ASC;

The return result is as follows:

+----+-----------+-------+----------------------+
| id | name      | score | DAY(enrollment_date) |
+----+-----------+-------+----------------------+
|  6 | Benjamin  |  96.5 |                    1 |
| 10 | Ethan     |    92 |                    1 |
|  2 | William   |  90.5 |                    2 |
|  3 | Olivia    |  95.5 |                    3 |
|  4 | James     |  87.5 |                    3 |
|  5 | Sophia    |  91.5 |                    5 |
|  7 | Ava       |  89.5 |                    6 |
|  9 | Charlotte |    88 |                    6 |
|  8 | Michael   |  93.5 |                    8 |
+----+-----------+-------+----------------------+
9 rows in set

LIMIT clause

Limit the number of rows in the result set

In SQL queries, the LIMIT clause can be used to limit the number of rows returned in the result set.

The LIMIT clause has two different syntaxes:

LIMIT [offset,] row_count

LIMIT row_count OFFSET offset

The parameters are described as follows:

• offset: the offset, which is the number of rows to skip. In the first format, offset is optional and defaults to 0, meaning no rows are skipped. The value range of offset is [0, +∞).

• row_count: the number of rows to return. In the first format, if offset is not specified, it defaults to starting from the first row. The value range of row_count is [0, +∞).

Query the first m rows of records

Example 24: Query the first five records of the id and name columns in the student table.

SELECT id, name
FROM student
LIMIT 5;

The return result is as follows:

+----+---------+
| id | name    |
+----+---------+
|  1 | Emma    |
|  2 | William |
|  3 | Olivia  |
|  4 | James   |
|  5 | Sophia  |
+----+---------+
5 rows in set

Query the record with the highest score

Example 25: To get the record with the highest score in the student table, you can first sort by score in descending order and then retrieve the first record.

SELECT id, name, score
FROM student
ORDER BY score DESC
LIMIT 1;

The return result is as follows:

+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  6 | Benjamin |  96.5 |
+----+----------+-------+
1 row in set

Query m records after skipping n records

Example 26: Query the data of the id and name columns after the fifth record in the student table.

SELECT id, name
FROM student
LIMIT 3 OFFSET 5;

The return result is as follows:

+----+----------+
| id | name     |
+----+----------+
|  6 | Benjamin |
|  7 | Ava      |
|  8 | Michael  |
+----+----------+
3 rows in set

Paging queries

In SQL queries, the LIMIT clause can be used to implement paging queries.

The format for paging queries with the LIMIT clause is as follows:

LIMIT (page_no - 1) * page_size, page_size;

The parameters are described as follows:

• page_no: the page number, which starts from 1. The value range is [1,+∞).

• page_size: the number of records displayed on a page. The value range is [1,+∞). For example, if page_no = 5 and page_size = 10, you can obtain 10 records from the fifth page.

Example 27: In the student table, display 2 records per page and retrieve the data for the first, second, and third pages sequentially.

The first page:

SELECT id, name
FROM student
ORDER BY id
LIMIT 0,2;

The return result is as follows:

+----+---------+
| id | name    |
+----+---------+
|  1 | Emma    |
|  2 | William |
+----+---------+
2 rows in set

The second page:

SELECT id, name
FROM student
ORDER BY id
LIMIT 2,2;

The return result is as follows:

+----+--------+
| id | name   |
+----+--------+
|  3 | Olivia |
|  4 | James  |
+----+--------+
2 rows in set

The third page:

SELECT id, name
FROM student
ORDER BY id
LIMIT 4,2;

The return result is as follows:

+----+----------+
| id | name     |
+----+----------+
|  5 | Sophia   |
|  6 | Benjamin |
+----+----------+
2 rows in set

References

• For more information about the SELECT statement, see SELECT.

• For more information about comparison operators, see Comparision operators.

• For more information about subqueries, see Subqueries.

• For more information about query optimization, see Rule-based query rewrite.