Top, Limit, Fetch First, and Row Number Keywords in SQL

Introduction

This presentation focuses on the keywords Top, Limit, Fetch First, and Row Number in SQL. These keywords are important for specifying or restricting the number of records returned by an SQL query. The use of these keywords varies across different database management systems as they are vendor-specific.

Importance of Keywords

• Purpose: Specify the number of records to return when executing a SQL query.
• Vendor-Specific: Different systems support different keywords; understanding this is crucial.
  • Microsoft SQL Server: Supports Top.
  • MySQL: Supports Limit.
  • Oracle: Supports Fetch First N Rows Only and Row Number.

Examples

Example 1: Using Top in Microsoft SQL Server

• Query: SELECT TOP 3 * FROM employee
• Output: Returns the first three rows from the employee table.
• Note: Top is available in Microsoft SQL Server and Microsoft Access.

Example 2: Using Top with Percentage

• Query: SELECT TOP 50% * FROM employee
• Output: Returns 50% of the records. If the table has 10 records, it returns the first five rows.

Example 3: Using Limit in MySQL

• Query: SELECT * FROM employee LIMIT 3
• Output: Equivalent to Example 1; returns the first three rows.
• Note: Limit is used in MySQL.

Example 4: Using Fetch First in Oracle

• Query: SELECT * FROM employee ORDER BY Department FETCH FIRST 3 ROWS ONLY
• Output: Returns the first three rows ordered by the department.
• Note: Applicable in Oracle.

Example 5: Using Row Number in Oracle

• Query: SELECT * FROM employee WHERE rownum <= 3
• Output: Returns the first three rows.
• Note: Difference between Fetch First and Row Number depends on Oracle version.

Conclusion

Understanding these keywords and which database management systems support them is essential for effectively limiting records in SQL queries. Always refer to the documentation to verify compatibility with the system version you are using.

Thanks

I hope the session is informative. Thank you for watching.