FETCH in SQL
Last Updated :
19 May, 2021
FETCH is an SQL command used along with ORDER BY clause with an OFFSET(Starting point) to retrieve or fetch selected rows sequentially using a cursor that moves and processes each row one at a time till the number of rows mentioned in the query are displayed.
- With FETCH the OFFSET clause is mandatory. You are not allowed to use, ORDER BY … FETCH.
- You are not allowed to combine TOP with OFFSET and FETCH.
- The OFFSET/FETCH row count expression can only be any arithmetic, constant, or parameter expression which will return an integer value.
- With the OFFSET and FETCH clause, the ORDER BY is mandatory to be used.
Syntax:
SELECT *
FROM table_name
ORDER BY col_name
OFFSET starting point
FETCH NEXT k(constant) ROWS ONLY;
Steps to implement FETCH:
Here, we will discuss the steps to implement the FETCH command in SQL.
Step 1: Reference table:
Let us consider a table is created based on marks of students in the class that contains data displayed below.
ID |
NAME |
MATHEMATICS |
PHYSICS |
CHEMISTRY |
501 |
Surya |
99 |
97 |
85 |
502 |
Sravan |
91 |
98 |
94 |
503 |
Charan |
99 |
93 |
88 |
504 |
Ram |
92 |
99 |
92 |
505 |
Aryan |
94 |
99 |
88 |
506 |
Sathwik |
91 |
88 |
91 |
507 |
Madhav |
90 |
97 |
89 |
Step 2: Creating a database:
CREATE DATABASE gfg;
Step 3: Using the database:
USE gfg;
Step 4: Creating a table:
Creating table MarketList with 5 columns using the following SQL query:
CREATE TABLE MarkList
(
id int,
name varchar(20),
mathematics int,
physics int,
chemistry int
);
Step 5: Inserting the data into the table:
INSERT INTO MarkList VALUES(501,'Surya',99,97,85);
INSERT INTO MarkList VALUES(502,'Charan',99,93,88);
INSERT INTO MarkList VALUES(503,'Sravan',91,98,94);
INSERT INTO MarkList VALUES(504,'Ram',92,99,82);
INSERT INTO MarkList VALUES(505,'Aryan',94,99,88);
INSERT INTO MarkList VALUES(506,'Sathwik',91,88,91);
INSERT INTO MarkList VALUES(507,'Madhav',90,97,89);
Step 6: After inserting, the table will look like this.
Step 7: Now fetching the data using the fetch command:
To retrieve the names, id and total marks of the top 3 students, the fetch command is used as follows:
SELECT Id, name, mathematics+physics+chemistry AS total
FROM MarketList
ORDER BY mathematics+physics+chemistry DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY;
So, the SQL FETCH command is used to retrieve selected rows from a table sequentially. It’s handy to use when you want to select a limited number of rows from an ordered set, like top 3, top 10, or bottom 3, etc.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...