AdventureWorks Database: Skip a number of rows from a sorted table

SQL Query - AdventureWorks: Exercise-35 with Solution

35. From the following table write a query in SQL to skip the first 10 rows from the sorted result set and return all remaining rows.

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 and skipping the first 10 rows
-- OFFSET clause is used to specify the number of rows to skip
-- In this case, 10 rows will be skipped
ORDER BY DepartmentID OFFSET 10 ROWS;

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 a specified number of rows before starting to return rows from the result set.
• In this query, it skips the first 10 rows before returning any rows.
• The result set will be sorted by DepartmentID and start from the 11th row onwards.

Sample Output:

departmentid|name                      |groupname                           |
------------+--------------------------+------------------------------------+
          11|Information Services      |Executive General and Administration|
          12|Document Control          |Quality Assurance                   |
          13|Quality Assurance         |Quality Assurance                   |
          14|Facilities and Maintenance|Executive General and Administration|
          15|Shipping and Receiving    |Inventory Management                |
          16|Executive                 |Executive General and Administration|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Using rank function with order by clause.
Next: Fetch rows from the middle of a sorted table.