FETCH in SQL
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.