By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

SQL on DB2 - restrict number of rows returned

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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
AnnieLab wrote:
In Oracle you can restrict the number of rows returned by specifying
'WHERE
ROWNUM < 10'. Can you do this in DB2 as well?


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
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.