SQL FETCH FIRST clause in DB2 for i SQL

FETCH FIRST clause in DB2 for i SQL

FETCH FIRST clause is used to specify the number of rows to return in the SELECT statement.

Syntax using FETCH FIRST clause

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY

Example #1: Select the first 2 records of the STUDENT table:

Refer STUDENT table from here.

Query:

SELECT * FROM Student 
FETCH FIRST 2 ROWS ONLY

Output:

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202

Example #2: Select the first 2 records of the STUDENT table where ROLLNO is greater than 2:

Refer STUDENT table from here.

Query:

SELECT * FROM Student
where rollno > 2     
FETCH FIRST 2 ROWS ONLY

Output:

ROLLNO   NAME                      SUBJECTID
     3   Raman                           203
     4   Vimal                           204

Example #3: Select the first 2 records of the STUDENT table order by ROLLNO in descending order:

Refer STUDENT table from here.

Query:

SELECT * FROM Student
order by rollno desc 
FETCH FIRST 2 ROWS ONLY

Output:

ROLLNO   NAME                      SUBJECTID
     5   Samar                           290
     4   Vimal                           204

Example #4: Select the first record of the STUDENT table:

Refer STUDENT table from here.

Query: All below syntax are valid.

SELECT * FROM Student 
FETCH FIRST ROW ONLY

Or

SELECT * FROM Student 
FETCH FIRST ROWS ONLY

Or

SELECT * FROM Student 
FETCH FIRST 1 ROWS ONLY

Or

SELECT * FROM Student 
FETCH FIRST 1 ROW ONLY

Output:

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201