Limiting Result Sets with ROWS

Go Up to Understanding Data Retrieval with SELECT

The ROWS clause permits you to acquire a portion of the result set by specifying a number of rows, a range of rows, or a percent of rows. You can also see only every nth row and you can use the WITH TIES keyword to see duplicate rows. ROWS is most often used in conjunction with ORDER BY.

The complete syntax of the ROWS clause is:

ROWS <value> [TO <upper_value>] [BY <step_value>][PERCENT][WITH TIES]

The ROWS clause subsets the number of rows from the result set of a table expression. This feature is useful in contexts where results must be returned in sequential chunks, and is therefore of interest to Web developers who need to parcel pieces of a larger result set from the Web server to a client browser. This type of Web application has a stateless interface with the database server and cannot gradually scroll the full result set via a cursor or download the entire result set into a cached dataset on the client. Rather the same SQL query is iteratively submitted to the database server but with a ROWS clause to specify which numbered rows from the full result set should be returned.

The ROWS clause has several optional elements that produce a variety of results, listed in the following table:

Expression	Returns
ROWS n	Returns the first <n> rows of the result set, or <n> percent if used with PERCENT
ROWS m TO n	Returns rows <m> through <n>, inclusive or the <m>th to <n>th percent
ROWS n BY p	Returns every <p>th row of the first <n> rows
ROWS m TO n BY p	Returns every <p>th row of rows <m> though <n>
ROWS n PERCENT	Returns the first <n> percent of the result set You can substitute any legal “ROWS” syntax for the “ROWS <n>” portion of this example; PERCENT applies to all values given for ROWS
ORDER BY … ROWS n WITH TIES	WITH TIES returns additional duplicate rows when the last value in the ordered sequence is the same as values in subsequent rows of the result set; must be used in conjunction with ORDER BY When a number of rows is specified, duplicate rows returned with TIES count as a single row You can substitute any legal “ROWS” syntax for the “ROWS <n>” portion of this example

Advance To:

• Appending Tables with UNION