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.
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.
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:
select * from registrations;
Now we are all set to try a few examples with the help of this table.
Basic FETCH NEXT commands
a. Find the user details in the first row of the registrations table.
SELECT * FROM registrations FETCH NEXT ROWS ONLY;
b. Find the username and city corresponding two the first two rows in the table.
SELECT username,city FROM registrations FETCH NEXT 2 ROWS ONLY;
FETCH NEXT with OFFSET
Find the username and city corresponding to the first three records starting from the third row in the registrations table.
SELECT username,city FROM registrations OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;
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).
SELECT username, city, contact_no FROM registrations ORDER BY username OFFSET 5 ROWS FETCH NEXT 2 ROWS ONLY;
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.
SELECT username, city, contact_no FROM registrations WHERE city = 'Mumbai' ORDER BY username OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
FETCH NEXT with GROUP BY clause
Find the city with the highest number of total registrations.
SELECT city, count(regis_id) FROM registrations GROUP BY city ORDER BY count(regis_id) DESC FETCH NEXT 1 ROW ONLY;
FETCH NEXT and CURSORS
Create a cursor for searching the registrations table in descending order of regis_id.
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.
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.
We hope that this EDUCBA information on “SQL FETCH NEXT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.