CODEWITHSUNDEEP
sqldb2db2-luw
Sourav Mehra
Sourav Mehra

Reputation: 445

DB2 rownum equivalent

I have the below query in Oracle which I want to replicate in DB2 which uses the random function and the rownum.

Oracle Query : 

SELECT * 
FROM  (
   SELECT * 
   FROM db2admin.QUESTION_BANK 
   WHERE type='PROCESS' 
   ORDER BY dbms_random.value
) WHERE rownum <=?

I got alternate for random function but nothing for rownum. Below is query in DB2,

SELECT * 
FROM  (
  SELECT * 
  FROM db2admin.QUESTION_BANK 
  WHERE type='PROCESS' ORDER BY RAND
) WHERE rownum <= ?

The value for rownum is passed via PreparedStatement.

Upvotes: 2

Views: 20325

Answers (4)

Adam Silenko
Adam Silenko

Reputation: 3108

use ROW_NUMBER() analitical function as equivalent of rownum

SELECT * 
FROM  (
   SELECT db2admin.* 
   , ROW_NUMBER() OVER () rownum 
   FROM db2admin.QUESTION_BANK 
   WHERE type='PROCESS' 
   ORDER BY dbms_random.value
) WHERE rownum <=?

or better yet, you can use LIMIT clausule

SELECT db2admin.* 
, ROW_NUMBER() OVER () rownum 
FROM db2admin.QUESTION_BANK 
WHERE type='PROCESS' 
ORDER BY dbms_random.value
LIMIT :number_of_skiped_rows , :number_of_displayed_rows;

Upvotes: 1

Esperento57
Esperento57

Reputation: 17472

try it:

SELECT * 
FROM  (
       SELECT f1.*, rownumber() over(ORDER BY f1.randm ) as rownum
       from ( 
            select f0.*, rand() as randm FROM db2admin.QUESTION_BANK f0
             WHERE f0.type='PROCESS'
            ) f1
      ) f2 
WHERE f2.rownum <= ?

Upvotes: 2

user330315
user330315

Reputation:

You can use this:

SELECT * 
FROM db2admin.QUESTION_BANK 
WHERE type = 'PROCESS' 
ORDER BY RAND() 
fetch first ? rows only;

Upvotes: 3

data_henrik
data_henrik

Reputation: 17118

DB2 has ROWNUM when you work in the Oracle compatibility mode. To enable just this feature, use this:

db2set DB2_COMPATIBILITY_VECTOR=01
db2stop
db2start

To get all the Oracle features, enable it like this:

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start

The doc on the DB2_COMPATIBILITY_VECTOR has details on alternatives like ROW_NUMBER() OVER().

Upvotes: 3

Related Questions