Difference between TOP, OFFSET & OFFSET with FETCH

The TOP will help to display the no.of records

The example query to use TOP. The following query will display only top 10 records.

SELECT TOP(10) SalesOrderID AS SOID, SalesOrderDetailID AS SODID,

OrderQty AS [orderQuantity], UnitPrice as pricePerUnit

  FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

  WHERE SalesOrderDetailID < 10000

  order by SODID desc

Logical query processing when we are using TOP with ORDER BY:

  First FROM clause will execute

  Second WHERE clause will execute 

  Third SELECT clause will execute

  Fourth ORDER BY clause will execute

  Fifth TOP will execute 

  

-- How to skip the top no.of records from the result set?

We can use OFFSET to skip the records.

Follow these rules to use OFFSET

a. The OFFSET should be required to use ORDER BY clause in the query

b. TOP will not work with OFFSET

Example query with OFFSET: The following query will skip the first 10 records.

SELECT SalesOrderID AS SOID, SalesOrderDetailID AS SODID,

OrderQty AS [orderQuantity], UnitPrice as pricePerUnit

  FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

  WHERE SalesOrderDetailID < 10000

  order by SODID desc

  offset 10 rows

-- Logical query processing with OFFSET

  First FROM clause will execute

  Second WHERE clause will execute 

  Third SELECT clause will execute

  Fourth ORDER BY clause will execute

  Fifth OFFSET will execute 

-- Display the required no.of records after skipping rows with OFFSET

We can use the FETCH to limit the no.of records after OFFSET.

Example query below FETCH only the 15 records after skipping first 10 records.

SELECT SalesOrderID AS SOID, SalesOrderDetailID AS SODID,

OrderQty AS [orderQuantity], UnitPrice as pricePerUnit

  FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

  WHERE SalesOrderDetailID < 10000

  order by SODID desc

  offset 10 rows 

  fetch next 15 rows only

-- Logical query processing with OFFSET & FETCH

  First FROM clause will execute

  Second WHERE clause will execute 

  Third SELECT clause will execute

  Fourth ORDER BY clause will execute

  Fifth OFFSET will execute

  Sixth FETCH will execute 

Thanks and Regards

Chreddy-SQL Trainer

Crystalspiders Institute

+91-733 845 7517

Whats-app: +91 955 313 7753

www.crystalspiders.com

About the trainer: https://www.linkedin.com/in/chreddy-sql-trainer

https://www.facebook.com/crystalspidersinstitute/

https://plus.google.com/+SQLTrainingByChreddy

https://www.youtube.com/channel/UCLtr2-cJvvVhvitoxf7hThw

https://twitter.com/crystalspiders