EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL FETCH NEXT

SQL FETCH NEXT

Priya Pedamkar
Article byPriya Pedamkar

Updated March 10, 2023

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.

ADVERTISEMENT
Popular Course in this category
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

SQL FETCH NEXT 1

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:

SQL FETCH NEXT 2

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:

Find the username and city

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:

first three records starting from the third row in the registrations table

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:

SQL FETCH NEXT 5

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:

corresponding to the first two records

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:

highest number of total registrations

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

We hope that this EDUCBA information on “SQL FETCH NEXT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL DATEPART()
  2. SQL Users
  3. SQL SELECT DISTINCT
  4. Column in SQL
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
63+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW