SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial
SQL SELECT TOP clause is used to specify the number of records to be returned. The SELECT TOP clause is used on large tables having thousands of records because returning a very large number of records that can impact on the performance. The operation performed by TOP, LIMIT, and ROWNUM clause has almost the same functionality.
SQL TOP, LIMIT Or ROWNUM Clause
In some situations, you may not be interested in all of the rows returned by a query, for example, if you want to retrieve the top 10 employees who have recently joined the organization and get the top 3 students by score, or something like that.
If we want to handle such situations, you can use the SQL’s TOP clause in the SELECT statement. However, a TOP clause is only supported by the SQL Server and MS Access database systems.
Not all the database systems support the SELECT TOP clause.
MySQL supports the LIMIT clause to select a limited number of records.
Oracle uses ROWNUM.
#SYNTAX: (For SQL SERVER)
SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;
- TOP number: The numbers of records to be retrieved.
- TOP percent: Percentage of records to be retrieved.
- Table_name: Name of the table.
- Condition: Condition to be imposed on the Select statement.
#Syntax: (For Oracle)
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;
See the syntax of MySQL Databases.
#Syntax: (For MySQL databases)
SELECT column_name(s) FROM table_name WHERE condition LIMIT number;
Let’s understand all these with examples.
Consider table: CUSTOMERS
QUERY: (For SQL SERVER)
Select Top 3 * From Customers;
So, here the first three records are displayed as we have used the Top number clause.
#SQL TOP PERCENT Example
The following SQL statement selects the first 50% of the records from the “Customers” table.
SELECT TOP 50 PERCENT * FROM Customers;
#QUERY: (For MySQL databases)
Select * from Customers where ID >= 1 LIMIT 3;
So, here, the records whose Id’s are more than 1 and is equal to 1 are displayed up to limit 3.
#QUERY: (For Oracle)
SELECT * FROM Customers WHERE ROWNUM <= 3;
#SQL LIMIT along with OFFSET
LIMIT an OFFSET b means skip the first b entries and then return the next entries.
OFFSET can only be used with an ORDER BY clause. It cannot be used on its own.
An OFFSET value must be greater than or equal to zero. It cannot be negative, else it returns the error.
See the following syntax.
SELECT expressions FROM tables [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] LIMIT number_rows [ OFFSET offset_value ];
#Parameters or Arguments
- The columns that you wish to retrieve.
- The tables that you wish to retrieve the records from. There must be at least one table listed in a FROM clause.
- #WHERE conditions
- Optional. The conditions that must be met for the records to be returned.
- #ORDER BY expression
- Optional. It is used in a SELECT LIMIT statement so that you can order the results and target those that you wish to return. The ASC is ascending order and DESC which means descending order.
- #LIMIT number_rows
- It specifies the limited number of rows in the result set to be returned based on the number_rows. Let’s say, LIMIT 11 would return the first 11 rows matching the SELECT criteria. This is where the sorting order matters, so you need to be sure to use the ORDER BY clause appropriately.
- #OFFSET offset_value
- Optional. The first row returned by the LIMIT will be determined by offset_value.
#SQL LIMIT ALL
The LIMIT ALL clause implies no limit. See the following syntax.
SELECT * FROM Student LIMIT ALL;
The above query returns all the entries in the table.
Finally, SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial is over.