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.
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:
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.