SQL SELECT TOP, LIMIT, ROWNUM

SQL SELECT TOP clause

• The SELECT TOP clause specifies the number of records to return.
  
• Select TOP clauses are useful on large tables with thousands of records. R eturning a large number of records can affect performance.
  

Note: Not all database systems support the SELECT TOP clause. MySQL supports LIMIT clauses to select a limited number of records, while Oracle uses ROWNUM.

SQL Server / MS Access syntax

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

SQL SELECT TOP in MySQL and Oracle is equivalent

MySQL syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Instance

SELECT *
FROM Persons
LIMIT 5;

Oracle syntax

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Instance

SELECT *
FROM Persons
WHERE ROWNUM <=5;

Demonstrate the database

In this tutorial, we'll use the famous Northwind sample database.

Here's the data from the Customers table:

SQL SELECT TOP instance

The following SQL statement selects the first two records from the Customers table:

SQL SELECT TOP PERCENT instance

The following SQL statement selects the top 50% of records from the "Customers" table:

SQL TOP, LIMIT, and ROWNUM examples

The following SQL statement selects the first three records from the Customers table:

SELECT TOP 3 * FROM Customers;

The following SQL statement shows an equivalent example of using the LIMIT clause:

SELECT * FROM Customers
LIMIT 3;

The following SQL statement shows an equivalent example of using ROWNUM:

SELECT * FROM Customers
WHERE ROWNUM <= 3;

SQL TOP PERCENT example

The following SQL statement selects the first 50% of records from the Customers table:

SELECT TOP 50 PERCENT * FROM Customers;

Add a condition

The following SQL statement selects the first three records with the country as "Germany" from the "Customers" table:

SELECT TOP 3 * FROM Customers
WHERE Country='Germany';

The following SQL statement shows an equivalent example of using the LIMIT clause:

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

The following SQL statement shows an equivalent example of using ROWNUM:

SELECT * FROM Customers
WHERE Country='Germany' AND ROWNUM <= 3;

Why LIMIT your query results

LIMIT as a simple picing method, mainly to reduce the time of data return, if you query a very large table (for example, a table with hundreds of thousands or millions of rows) without using limits, then you may wait a long time to display all the results, so using LIMIT can reduce the time and efficiency of query data return.

Chapter test

Now, use the following topics to measure your mastery of the content of this chapter!

SQL LIMIT keywords: In this section of the quiz, you will practice using the SQL LIMIT keyword to return all results that meet the criteria specified in the SQL statement.

Click here to test

Note: The above tests are paid and premium VIP is free of charge