Blog Archive

Db2 equivalent of MySQL limit and offset to fetch certain rows only

How to achieve MySQL Offset,LIMIT in Db2? Let say you want to fetch 10 rows starting from row 11 in MySQL. The query in MySQL is defined as follows
select 
    customer_id,
    last_name,
    first_name,
    email,
    active
from
    sakila.customer c 
order by last_name desc ,first_name desc
limit 10,10;

Now to do the same in Db2 you have to use the ROW_NUMBER() OVER () functions. I didn't have the same table in my AS400(IBM i) Db2 database but you will get the idea. Here is the code snippet
SELECT * from (SELECT p.*,row_number() over() as rn   
from products as p) as col                             
where rn between 1  and 20 

Equivalent of MySQL limit offset in Db2 database
Now if you would like to sort the same result in a different order then you need to put the ORDER by clause in the over() function as shown below
SELECT * from (SELECT p.*,row_number()         
over(ORDER by weight desc) as rn               
from products as p) as col                     
where rn between 11 and 20        


Equivalent of MySQL limit offset in Db2 database
Please Note: This works in Oracle database also but I haven't tried it.