AppDividend
Latest Code Tutorials

SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial

0

SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial is today’s topic. 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 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 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;

#Parameters

  1. TOP number: The numbers of records to be retrieved.
  2. TOP percent: Percentage of records to be retrieved.
  3. Table_name: Name of the table.
  4. 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&nbsp;column_name(s)
FROM&nbsp;table_name
WHERE&nbsp;condition
LIMIT&nbsp;number;

Let’s understand all these with examples.

Consider table: CUSTOMERS

ID NAME AGE ADDRESS SALARY 
1 Tom 21 Kolkata 500
2 Karan 22 Allahabad 600
3 Hardik 23 Dhanbad 700
4 Komal 24 Mumbai 800

 

QUERY: (For SQL SERVER)

Select Top 3 * From Customers;

Output:

ID NAME AGE ADDRESS SALARY 
1 Tom 21 Kolkata 500
2 Karan 22 Allahabad 600
3 Hardik 23 Dhanbad 700

 

So, here the first three records are displayed as we have used 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;

#Output:

ID NAME AGE ADDRESS SALARY 
1 Tom 21 Kolkata 500
2 Karan 22 Allahabad 600
3 Hardik 23 Dhanbad 700

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;

#Output:

ID NAME AGE ADDRESS SALARY 
1 Tom 21 Kolkata 500
2 Karan 22 Allahabad 600
3 Hardik 23 Dhanbad 700

 

#Using LIMIT along with OFFSET

LIMIT a OFFSET b means skip the first b entries and then return the next a 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 ];

In the above query, we are using the SELECT, WHERE, ORDER BY, and LIMIT Clause.

#Parameters or Arguments

#expressions

The columns that you wish to retrieve.
#tables
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.

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

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.