Open In App

FETCH in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

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.


Last Updated : 19 May, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads