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.
Share your thoughts in the comments
Please Login to comment...