w3resource

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.

Sample table: HumanResources.Department
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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.