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.
The following example groups the NavStateRecords by the SalesRep attribute, and returns result records 11-20:
DEFINE Reps AS 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.
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 GROUP BY SalesRep ORDER BY Total DESC PAGE (0,10)
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 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) |