Limiting Record Number on DB2

DB2 has capabilities to limit recordset retrieved from ‘select’ statement. In these example, I use SAMPLE database with DB2 LUW.
To limit show only several top records use

select * from employee fetch first 10 rows only

This will show only to 10 records from employee table.

Also we can display sequence row number. Here’s example of sql code of employee with related projects:

select firstnme, lastname, deptname, projname, actdesc, row_number() over() as rn from employee
inner join empprojact on employee.empno = empprojact.empno
inner join project on empprojact.projno = project.projno
inner join act on empprojact.actno = act.actno
inner join department on employee.workdept = department.deptno

Above code will show related records with its row number.

To filter specific record number use:

select * from (select firstnme, lastname, deptname, projname, actdesc,row_number() over() as rn from employee
inner join empprojact on employee.empno = empprojact.empno
inner join project on empprojact.projno = project.projno
inner join act on empprojact.actno = act.actno
inner join department on employee.workdept = department.deptno) as tables where rn between 1 and 50

Above sql will show related records with row number 1 until 50. This sql code can be use to do paging in web page instead of retrieve one large recordset.

Regards,
Agung Gugiaji

Advertisements

One response to “Limiting Record Number on DB2

  1. Pingback: PHP Example Retrieve DB2 Records Per Page | Enlighten Application Developer Journals

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s