SQL SELECT TOP, LIMIT, ROWNUM
SQL SELECT TOP clause
SELECT TOP clause is used to provide for the number of records returned.
SELECT TOP clause to have thousands of records of a large table, it is very useful.
Note: Not all database systems support the SELECT TOP clause.
SQL Server / MS Access Syntax
SELECT TOP number | percent column_name(s)
FROM table_name ;
FROM table_name ;
MySQL and Oracle in the SQL SELECT TOP is equivalent to
MySQL syntax
SELECT column_name(s)
FROM table_name
LIMIT number ;
FROM table_name
LIMIT number ;
Examples
SELECT *
FROM Persons
LIMIT 5;
FROM Persons
LIMIT 5;
Oracle Syntax
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number ;
FROM table_name
WHERE ROWNUM <= number ;
Examples
SELECT *
FROM Persons
WHERE ROWNUM <=5;
FROM Persons
WHERE ROWNUM <=5;
The demo database
In this tutorial, we will use w3big sample database.
The following is a selected "Websites" table data:
mysql> SELECT * FROM Websites; +----+---------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+---------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 本教程 | http://www.w3big.com/ | 5000 | USA | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | | 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | +----+---------------+---------------------------+-------+---------+
Examples of SQL SELECT TOP
The following SQL statement to select the first two records from the "Websites" table:
Examples
SELECT * FROM Websites LIMIT 2;
Execute the above SQL, with the following data:
SQL SELECT TOP PERCENT examples
In Microsoft SQL Server can also be used as an argument percentage.
The following SQL statement to select from in front of "Customers" table, 50% of the record:
Examples
Perform the following actions in Microsoft SQL Server database.
SELECT TOP 50 PERCENT * FROM Websites;