PAGE clause

The PAGE clause specifies a subset of records to return.

By default, a statement returns all of the result records. In some cases, however, it is useful to request only a subset of the results. In these cases, you can use the PAGE (<offset>, <count>) clause to specify how many result records to return:

The <offset> argument is an integer that determines the number of records to skip. An offset of 0 will return the first result record; an offset of 8 will return the ninth.
The <count> argument is an integer that determines the number of records to return.

Note that if <offset> is greater than the total number of available records, an empty table is returned. However, if <offset> + <count> is greater than the total number of available records, it returns as many records as it can.

The following example groups records by the SalesRep attribute, and returns result records 11-20:

DEFINE Reps AS
FROM ResellerState
GROUP BY SalesRep
PAGE (10,10)

PAGE applies to intermediate results; a statement FROM a statement with PAGE(0, 10) will have at most 10 source records.

Top-K

You can use the PAGE clause in conjunction with the ORDER BY clause in order to create Top-K queries. The following example returns the top 10 sales representatives by total sales:

DEFINE Reps AS
SELECT SUM(Amount) AS Total
FROM ResellerState
GROUP BY SalesRep
ORDER BY Total DESC
PAGE (0,10)

Percentile

The PAGE clause supports a PERCENT modifier. When PERCENT is specified, fractional offset and size are allowed, as in the example PAGE(33.3, 0.5) PERCENT. This specified the portion of the data set to skip and the portion to return.

The number of records skipped equals round(offset * COUNT / 100).

The number of records returned equals round((offset + size) * COUNT / 100) - round(offset * COUNT / 100).

DEFINE ModelYear AS
SELECT SUM(Cost) AS Cost
FROM ProductState
GROUP BY Model, Year
ORDER BY Cost DESC
PAGE(0, 10) PERCENT

The PERCENT keyword will not repeat records at non-overlapping offsets, but the number of results for a given page size may not be uniform across the same query.

For example, if COUNT = 6:

PAGE clause	Resulting behavior is the same as
PAGE (0, 25) PERCENT	PAGE (0, 2)
PAGE (25, 25) PERCENT	PAGE (2, 1)
PAGE (50, 25) PERCENT	PAGE (3, 2)
PAGE (75, 25) PERCENT	PAGE (5, 1)