Fetch first wk_search_max_rows rows only là gì năm 2024

The OFFSET clause and FETCH FIRST clause on the SELECT statement can be used to return a subset of rows from a result set.

Users can code the “n” value of a FETCH FIRST n or OFFSET n specification in a SELECT statement as a host language variable in embedded SQL applications, or as a parameter or local variable in a database procedure.

This feature is useful in Web-style applications that page results back to the user, as in the results from using a web search engine.

For example, the following query returns rows starting from the 25th row of the result set:

SELECT * FROM MYTABLE ORDER BY COL1 OFFSET 25

For example, the following query fetches only the first 10 rows of the result set:

SELECT * FROM MYTABLE ORDER BY COL1 FETCH FIRST 10 ROWS ONLY

A query can use any combination of the ORDER BY, OFFSET, and FETCH FIRST clauses, but in that order only.

The OFFSET and FETCH FIRST clauses can be used only once per query, and cannot be used in unions or view definitions. They cannot be used in subselects, except a subselect in a CREATE TABLE statement or an INSERT statement.

The FETCH FIRST clause cannot be used in the same SELECT statement as SELECT FIRST rowcount.

The syntax for each clause is OFFSET n or FETCH FIRST n, where n is a positive integer, a host variable, or a procedure parameter or local variable.

In the FETCH FIRST clause, the keywords FIRST and NEXT, and the keywords ROWS and ROW are interchangeable. Because you can offset and fetch first in the same query, NEXT is an alternative for readability. For example:

The OFFSET and FETCH FIRST clauses are used to return a specified range of rows beginning from a particular starting point in a result set. The ability to limit the rows retrieved from large result sets allows you to "page" through the data and improves efficiency.

The OFFSET clause indicates the number of rows to skip before starting to return data. If the OFFSET clause is not used in a SELECT statement, the starting row is 0. The FETCH FIRST clause specifies the number of rows to be returned, either as an unsigned integer greater than or equal to 1 or as a percentage, from the starting point indicated in the OFFSET clause. If both OFFSET and FETCH FIRST are used in a SELECT statement, the OFFSET clause should come first.

The OFFSET and FETCH FIRST clauses are not supported in subqueries.

OFFSET clause format

The OFFSET format is:

Copy

OFFSET [ n {ROWS | ROW} ]

FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]

9 is an unsigned integer. If

FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]

9 is larger than the number of rows returned in the result set, then nothing is returned and no error message appears.

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS

1 is the same as

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS

2.

FETCH FIRST clause format

The FETCH FIRST format is:

Copy

FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]

FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]

9 is the number of rows to be returned. The default value is 1 if

FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]

9 is omitted.

FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]

9 is an unsigned integer greater than or equal to 1 unless it is followed by

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS

7. If

FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]

9 is followed by

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS

7, the value may be either a positive fractional value or an unsigned integer.

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS

1 is the same as

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS

2.

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

2 must be used with the

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

3 clause.

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

2 allows more rows to be returned than specified in the

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

5 count value because peer rows, those rows that are not distinct based on the

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

3 clause, are also returned.

Examples

Return information from the twenty-sixth row of the result set sorted by

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

7 then by

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

8.