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

Last update on December 20 2024 11:57:37 (UTC/GMT +8 hours)

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.

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 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.