SQL on DB2 - restrict number of rows returned

In Oracle you can restrict the number of rows returned by specifying 'WHERE
ROWNUM < 10'. Can you do this in DB2 as well?

re: SQL on DB2 - restrict number of rows returned

AnnieLab wrote:
[color=blue]
> In Oracle you can restrict the number of rows returned by specifying
> 'WHERE
> ROWNUM < 10'. Can you do this in DB2 as well?[/color]

SELECT ... FROM ... WHERE ... FETCH FIRST 10 ROWS ONLY

Or use the ROW_NUMBER OLAP function:

SELECT ...
FROM ( SELECT ..., row_number() over() AS rn
FROM ...
WHERE ... ) AS t
WHERE rn < 10

Or use a UDF like the "counter" function available in the samples:

SELECT ...
FROM ...
WHERE counter() < 10

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

re: SQL on DB2 - restrict number of rows returned

Hi,

1. using FETCH FIRST clause
SELECT * FROM Table FETCH FIRST 10 ROWS ONLY

2. using rownumber OLAP function
SELECT * FROM (
SELECT col1,
rownumber() over(ORDER BY col1) AS rn
FROM Table) AS t
WHERE t.rn <=10

Regards
Adrian