AdventureWorks Database: Fetch rows from the middle of a sorted table

SQL Query - AdventureWorks: Exercise-36 with Solution

36. From the following table write a query in SQL to skip the first 5 rows and return the next 5 rows from the sorted result set.

departmentid|name                      |groupname                           |modifieddate           |
------------+--------------------------+------------------------------------+-----------------------+
           1|Engineering               |Research and Development            |2008-04-30 00:00:00.000|
           2|Tool Design               |Research and Development            |2008-04-30 00:00:00.000|
           3|Sales                     |Sales and Marketing                 |2008-04-30 00:00:00.000|
           4|Marketing                 |Sales and Marketing                 |2008-04-30 00:00:00.000|
           5|Purchasing                |Inventory Management                |2008-04-30 00:00:00.000|
           6|Research and Development  |Research and Development            |2008-04-30 00:00:00.000|
           7|Production                |Manufacturing                       |2008-04-30 00:00:00.000|
           8|Production Control        |Manufacturing                       |2008-04-30 00:00:00.000|
           9|Human Resources           |Executive General and Administration|2008-04-30 00:00:00.000|
          10|Finance                   |Executive General and Administration|2008-04-30 00:00:00.000|
          11|Information Services      |Executive General and Administration|2008-04-30 00:00:00.000|
          12|Document Control          |Quality Assurance                   |2008-04-30 00:00:00.000|
          13|Quality Assurance         |Quality Assurance                   |2008-04-30 00:00:00.000|
          14|Facilities and Maintenance|Executive General and Administration|2008-04-30 00:00:00.000|
          15|Shipping and Receiving    |Inventory Management                |2008-04-30 00:00:00.000|
          16|Executive                 |Executive General and Administration|2008-04-30 00:00:00.000|

Click to view Full table

Sample Solution:

-- Selecting specific columns from the Department table
SELECT DepartmentID, Name, GroupName  

-- From the HumanResources schema's Department table
FROM HumanResources.Department  

-- Ordering the results by DepartmentID
-- OFFSET clause is used to skip the first 5 rows in the ordered result set
-- FETCH NEXT clause is used to retrieve the next 5 rows after the offset
ORDER BY DepartmentID   
    OFFSET 5 ROWS  
    FETCH NEXT 5 ROWS ONLY;

Explanation:

• The SQL query retrieves data from the Department table within the HumanResources schema.
• It selects three columns: DepartmentID, Name, and GroupName.
• The ORDER BY clause specifies the sorting criteria for the result set, which is based on DepartmentID.
• The OFFSET clause is used to skip the first 5 rows in the ordered result set.
• The FETCH NEXT clause is used to retrieve the next 5 rows after the offset.
• In this query, it skips the first 5 rows and then retrieves the next 5 rows according to the specified ordering.
• The result set will be sorted by DepartmentID, and it will start from the 6th row and return the next 5 rows.

Sample Output:

departmentid|name                    |groupname                           |
------------+------------------------+------------------------------------+
           6|Research and Development|Research and Development            |
           7|Production              |Manufacturing                       |
           8|Production Control      |Manufacturing                       |
           9|Human Resources         |Executive General and Administration|
          10|Finance                 |Executive General and Administration|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Skip a number of rows from a sorted table.
Next: List all the products that are red or blue.