SQL FETCH NEXT

Introduction to SQL FETCH NEXT

FETCH is a command in standard query language (SQL) that is used to retrieve rows from a SELECT query based on the position of a cursor. When we use NEXT as direction in conjugation with FETCH, we get FETCH NEXT that retrieves the next single row. If there is no such row, then the command returns an empty result. We can use other directions such as PRIOR, FIRST, LAST, RELATIVE, ABSOLUTE along with FETCH command. All of them retrieve one single row in the specified direction. However, if we do not specify anything with the FETCH command, by default it will function as FETCH NEXT and will fetch the next row.

Syntax and parameters:

The basic syntax for using FETCH NEXT command in a SELECT query is as follow:

SELECT column_name1, column_name2, ...
FROM table_name
ORDER BY sort_expression
OFFSET n ROWS
FETCH NEXT m ROWS ONLY;

The parameters used in the above mentioned syntax are as follows:

• column_name1, column_name2, …: columns or fields that have to be fetched from the table.
• table_name: database table from which said columns have to be fetched from.
• sort_expression: sort_expression is completely optional but is frequently used along with FETCH commands. If you have to use it, specify the order and expression on the basis of which the result set has to be arranged.
• OFFSET n ROWS: offset is used to exclude first n records. It is used along with FETCH NEXT to get the desired window of rows. Suppose if we want to exclude the first 10 rows and start fetching from the 11th row then we have to set the offset field to 10.
• NEXT m ROWS: Next m is the direction in which coming m rows have to be fetched.

Here we have seen the basic essential syntax used with FETCH NEXT command. You may use WHERE, GROUP BY etc. based on your requirement.

Examples of SQL FETCH NEXT

In order to discuss a few examples on FETCH NEXT command, we require a dummy table. Ergo, let’s create a database table called “registrations”. This table contains regis_id, username, city etc. corresponding to each user. Here is the create table statement for the same.

Code:

CREATE TABLE registrations
(
regis_id SERIAL primary key,
username character varying(255),
city character varying(255),
contact_no character varying(50)
);

The table has been successfully created. Our next task is to insert a few records in it. We can use the following INSERT statement.

Code:

INSERT INTO registrations(
username, city, contact_no)
VALUES ('Mohit Kumar','Shimla','9999999999'),
('Divya Kumar','New Delhi','8989898989'),
('Biju Mathews','New Delhi','7979797979'),
('Kritika Sharma','Mumbai','6999999999'),
('Himanshi Paul','Shimla','7989989999'),
('Garima Sinha','Mumbai','5969596949'),
('Alice Zane','New Delhi','4949494949');

The data in the registration table looks something as follows:

Code:

select * from registrations;

Output:

Now we are all set to try a few examples with the help of this table.

Example #1

Basic FETCH NEXT commands

a. Find the user details in the first row of the registrations table.

Code:

SELECT *
FROM registrations
FETCH NEXT ROWS ONLY;

Output:

b. Find the username and city corresponding two the first two rows in the table.

Code:

SELECT username,city
FROM registrations
FETCH NEXT 2 ROWS ONLY;

Output:

Example #2

FETCH NEXT with OFFSET

Find the username and city corresponding to the first three records starting from the third row in the registrations table.

Code:

SELECT username,city
FROM registrations
OFFSET 2 ROWS
FETCH NEXT 3 ROWS ONLY;

Output:

Example #3

FETCH NEXT with ORDER BY clause

Find the username, city and contact_no corresponding to the next two records starting from the sixth row in the ascendingly ordered registrations table (by username).

Code:

SELECT username, city, contact_no
FROM registrations
ORDER BY username
OFFSET 5 ROWS
FETCH NEXT 2 ROWS ONLY;

Output:

Example #4

FETCH NEXT with WHERE clause

Find the username, city and contact_no corresponding to the first two records such that the users belong to Mumbai.

Code:

SELECT username, city, contact_no
FROM registrations
WHERE city = 'Mumbai'
ORDER BY username
OFFSET 0 ROWS
FETCH NEXT 2 ROWS ONLY;

Output:

Example #5

FETCH NEXT with GROUP BY clause

Find the city with the highest number of total registrations.

Code:

SELECT city, count(regis_id)
FROM registrations
GROUP BY city
ORDER BY count(regis_id) DESC
FETCH NEXT 1 ROW ONLY;

Output:

Example #6

FETCH NEXT and CURSORS

Create a cursor for searching the registrations table in descending order of regis_id.

Code:

DECLARE search_cursor cursor WITH HOLD FOR
SELECT username, city, contact_no
FROM registrations
ORDER BY regis_id DESC;

In this example, we have created a cursor called “search_cursor” on the registrations table.

Conclusion

In this post, we have seen the FETCH NEXT command in SQL that is used to retrieve the next specified number of rows from the result set of a SELECT statement.

Recommended Articles

This is a guide to SQL FETCH NEXT. Here we discuss the introduction to SQL FETCH NEXT along with the examples respectively. You may also have a look at the following articles to learn more –

1. SQL DATEPART()
2. SQL Users
3. SQL SELECT DISTINCT
4. Column in SQL