Home » SQL Server OFFSET FETCH

SQL Server OFFSET FETCH

SQL Server OFFSET FETCH clauses are used to set the limit to number of rows returned by a query.  




OFFSET and FETCH clause are always used with Order By clause. It is an optional to use with order by clause. Following are the definition of OFFSET and FETCH clause.

OFFSET – It specifies the number of rows to skip before it starts to return rows from the query expression. It is mandatory.

FETCH – It specifies the number of rows to return after the OFFSET clause has been processed. It is an optional.

SYNTAX

SELECT  col1, col2, ....

FROM table_name

WHERE  conditions

ORDER BY column_list [ASC |DESC] 

OFFSET offset_row_count {ROW | ROWS} 

FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY

Lets take an example of using OFFSET and FETCH clause with order by clause

Here we have a sample table named as student

Create table Student 
(StudId int identity(1,1), 
StudName varchar(30), 
StudCode varchar(30))

insert into student values ('Rozer M', 'CodrozX3')
insert into student values ('Mahesh Singh', 'RodMSzG5')
insert into student values ('Sanjay Ak', 'SodAkzV4')
insert into student values ('Jack Peter', 'Jodrohn9')
insert into student values ('Suraj Rawat', 'Sodroag5')
insert into student values ('Munni M', 'Modfobj7')
insert into student values ('Honey',' Hodrihy9')
insert into student values ('Rahul Singh', 'Rodrihyd6')
insert into student values ('Mahesh uniyal', 'ModfihXFT')
insert into student values ('Nitesh Kumar', 'Nodtihu3')
insert into student values ('Munish Singh' ,'Muonirih56')

Lets fetch the student records from table and sort them in ascending order based on StudId column.

select * from student order by studId  

As you can see, now student records in sequence based on studid.

Use of OFFSET clause 

If you want to skip first 4 records from above query result set then you can use offset clause as given below.

select * from Student order by studId
OFFSET 4 ROWS

As you can see, it returns student records by skipping first 4 records from query result set.

Use of FETCH clause 

If you want to skip first 4 rows and select next 5 rows then, you use FETCH clause with OFFSET clause as shown in following query.

SELECT * FROM Student ORDER BY  studId
OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY

As you see, it skipped first 4 rows from query result set and select next 5 rows only.




Also Read..

SQL Server CTE (Common Table Expression)

SQL Recursive CTE

SQL Server Create Schema

SQL Server Synonyms

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 1,496 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.