100 Days of SQL

Day 10 – SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

SQL provides different ways to limit the number of rows returned in a query, depending on the database management system being used. Here are some examples:

1. TOP clause (used in SQL Server and Microsoft Access) The TOP clause is used to limit the number of rows returned by a query. The basic syntax is as follows:

SELECT TOP n column1, column2, ... FROM table_name WHERE condition;

Here, n is the maximum number of rows to be returned by the query, and column1, column2, etc. are the names of the columns to be selected from the table.

1. LIMIT clause (used in MySQL and PostgreSQL) The LIMIT clause is used to limit the number of rows returned by a query. The basic syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition LIMIT n;

Here, n is the maximum number of rows to be returned by the query, and column1, column2, etc. are the names of the columns to be selected from the table.

1. FETCH FIRST clause (used in Oracle and IBM DB2) The FETCH FIRST clause is used to limit the number of rows returned by a query. The basic syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition FETCH FIRST n ROWS ONLY;

Here, n is the maximum number of rows to be returned by the query, and column1, column2, etc. are the names of the columns to be selected from the table.

1. ROWNUM (used in Oracle) The ROWNUM is used to limit the number of rows returned by a query. The basic syntax is as follows:

SELECT column1, column2, ... FROM table_name WHERE ROWNUM <= n;

Here, n is the maximum number of rows to be returned by the query, and column1, column2, etc. are the names of the columns to be selected from the table.

Note that the specific syntax and functionality of these clauses may vary slightly depending on the database management system being used.