AdventureWorks Database: Fetch rows from the middle of a sorted table
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.Departmentdepartmentid|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|
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics