You can use Row_Limiting_Clause to limit the number of rows returned for a SELECT query. This feature is often used in pagination.
Syntax of Row_Limiting_Clause
Syntax of SQL queries that include Row_Limiting_Clause:
simple_select:
SELECT [ hint_options ] [ DISTINCT | UNIQUE | ALL] select_expr_list
FROM from_list
[WHERE condition]
[GROUP BY group_expression_list
[{ROLLUP | GROUPING SETS} group_expression_list]
[HAVING condition]
]
[ORDER BY order_expression_list]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
[row_limiting_clause ]
select_expr_list:
table_name.*
| table_alias_name.*
| expr [[AS] column_alias_name]
from_list:
table_reference [, table_reference...]
table_reference:
simple_table
| joined_table
| pivot_clause
| unpivot_clause
simple_table:
table_factor [partition_option] [[table_alias_name]
| (select_stmt) table_alias_name
| (table_reference_list)
joined_table:
table_reference [INNER] JOIN simple_table [join_condition]
| table_reference outer_join_type JOIN simple_table join_condition
partition_option:
PARTITION (partition_name_list)
partition_name_list:
partition_name [, partition_name...]
outer_join_type:
{LEFT | RIGHT | FULL} [OUTER]
join_condition:
ON expression
condition:
expression
group_expression_list:
group_expression [, group_expression...]
group_expression:
expression [ASC | DESC]
order_expression_list:
order_expression [, order_expression...]
order_expression:
expression [ASC | DESC]
row_limiting_clause:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
pivot_clause:
PIVOT
(aggregate_function ( expr ) [[AS] alias ]
[, aggregate_function ( expr ) [[AS] alias ]... ]
pivot_for_clause
pivot_in_clause
)
pivot_for_clause:
FOR { column| ( column [, column... ]) }
pivot_in_clause
IN
( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
[, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
)
unpivot_clause:
UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
( { column | ( column [, column... ]) }
pivot_for_clause
unpivot_in_clause
)
unpivot_in_clause:
IN
( { column | ( column [, column... ]) }[ AS { literal | ( literal [, literal... ]) } ]
[, { column | ( column [, column... ] ) }[ AS {literal | ( literal [, literal... ]) } ]]
)
Parameters
| Field | Description | |
|---|---|---|
| hint_options | Optional. The hint option. | |
| DISTINCT | UNIQUE | ALL | The returned data may contain duplicate rows.
|
|
| select_expr_list | The expression or names of columns to query from the database. Separate multiple names with commas (,). The value "*" indicates all columns.
|
|
| FROM table_references | The object from which data is selected. | |
| PARTITION(partition_list) | Specifies the partition information of the table to be queried. For example: partition(p0,p1…) |
|
| table_alias_name | The alias of the object from which data is selected, in the format of original name+space+alias. | |
| joined_table | The join method in a multi-table query.
|
|
| ON expression | The join condition in a multi-table join. | |
| WHERE where_conditions | optional. The filter condition. Only data that meets the condition is returned in the query results. where_conditions is an expression. |
|
| GROUP BY group_by_list | The grouping field, which is usually used together with aggregate functions.
Note |
|
| ROLLUP group_expression_list | Rolls up the results of the groups created by the GROUP BY clause and generates the statistics. | |
| GROUPING SETS group_expression_list | Specifies two or more data groups in a query, generates statistics, and aggregates and displays the statistics of the specified group. You can specify a single field or a list of fields in GROUPING SETS. |
|
| HAVING search_conditions | Filters the grouped data. The HAVING clause is similar to the WHERE clause, but the HAVING clause can reference an aggregate function such as SUM and AVG. |
|
| ORDER BY order_list | Sorts the query results by one or multiple columns in ascending (ASC) or descending (DESC) order. If you do not specify ASC or DESC, the default value ASC prevails.
|
|
| row_limiting_clause | Restricts the rows returned by a query, to implement querying by page. You can specify the offset and the number of rows or percentage of rows to return. It can be used together with the ORDER BY clause to ensure the sorting order, to obtain consistent results. |
|
| OFFSET | The number of rows to skip before a query by page begins. offset must be a numeric value or an expression that evaluates to a numeric value.
|
|
| ROW | ROWS | Indicates a single row or multiple rows. You can select the keyword based on the number of rows to ensure clear semantics. |
| FETCH | The number of rows or percentage of rows to return. If this clause is not specified, all rows starting from the (offset + 1)th row are returned. |
|
| FIRST | NEXT | Specifies the number or percentage of rows to return in the first or next response. |
| rowcount | percent PERCENT | Use rowcount to specify the number of rows to return. rowcount must be a numeric value or an expression that evaluates to a numeric value. If you specify a negative number, rowcount is taken as 0. If rowcount is greater than the number of available rows starting from the (rowcount +1)th row, all available rows are returned. If rowcount contains a fraction, the fraction is truncated. If rowcount is set to NULL, 0 rows are returned. Use percent PERCENT to specify the percentage of rows to return. percent must be a numeric value or an expression that evaluates to a numeric value. If you specify a negative number, percent is taken as 0. If percent is set to NULL, 0 rows are returned. If you do not specify rowcount or percentPERCENT, one row is returned. |
| ONLY | WITH TIES | ONLY specifies the number of rows or the percentage of rows to return. WITH TIES specifies to return other rows that have the same sorting key as the last row obtained. If you use WITH TIES, you must specify the ORDER BY clause. If you do not specify ORDER BY clause, no other rows will be returned. |
| FOR UPDATE | Imposes an exclusive lock on all the rows in the query results to prevent other concurrent transactions from changing or reading the rows in some transaction isolation levels.
Note |
|
| pivot_clause | Pivots a row to a column. | |
| aggregate_function | The aggregate function. | |
| expr | The expression whose calculation results are constant values. pivot_in_clause supports only constant expressions. |
|
| unpivot_clause | Pivots a column to a row. |
Examples of queries with Row_Limiting_Clause
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