Skip to content
Related Articles

Related Articles

Improve Article

FETCH in SQL

  • Difficulty Level : Medium
  • 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.

IDNAMEMATHEMATICSPHYSICSCHEMISTRY
 501Surya99 9785
502Sravan91 9894
503Charan99 9388
504Ram92 9992
505Aryan94 99 88
506Sathwik91 8891
507Madhav90 9789

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.

My Personal Notes arrow_drop_up
Recommended Articles
Page :