1.0

Example
SELECT AVG (QTY), CID FROM TRADE.PORTFOLIO GROUP BY CID HAVING
AVAILQTY > 150
-- Find the average QTY held by a customer grouped on Customer
ID,
-- only when the customer holds atleast two different stocks
SELECT AVG (QTY), CID FROM TRADE.PORTFOLIO GROUP BY CID HAVING
COUNT(*) > 2
OFFSET and FETCH FIRST Clauses
Limit the number of rows returned in a result set.
Syntax
OFFSET integer-literal { ROW | ROWS }
FETCH { FIRST | NEXT } [integer-literal] { ROW | ROWS } ONLY
Description
The OFFSET clause provides a way to skip a number of rows in a result set before returning any rows.
The FETCH FIRST clause, which can be combined with the OFFSET clause, limits the number of rows that are
returned in the result set. You can use the FETCH FIRST clause to retrieve only a few rows from a large result
set. FETCH FIRST is frequently used with an ORDER BY clause.
In both of these clauses, ROW is synonymous with ROWS and FIRST is synonymous with NEXT.
In the OFFSET clause the integer-literal must be equal to 0 (the default if no OFFSET clause is provided), or
it must be a positive number. If the OFFSET is larger than the number of rows in the result set, then no rows are
returned.
In the FETCH FIRST clause , the integer-literal must be 1 or greater. You can omit the integer-literal entirely
to use the default value of 1. With the default value, SQLFire returns all rows in the result set (or only those
rows that are remaining, if the OFFSET clause is used).
Examples
SELECT * FROM T FETCH FIRST ROW ONLY
SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS
ONLY
SELECT * FROM T OFFSET 100 ROWS
ORDER BY Clause
Specify the order in which rows appear in the ResultSet.
Syntax
ORDER BY { column-name | ColumnPosition | Expression }
[ ASC | DESC ]
[ , column-name | ColumnPosition | Expression
[ ASC | DESC ] ] *
491
SQL Language Reference