SQL Advanced Apps (top)

TOP

The TOP clause is used to specify the number of records to return. The TOP clause is useful for large tables with thousands of records.

However, not all database systems support the TOP clause

Syntax for SQL Server

SELECT TOP number|percent column_name (s) from table_name

The SQL SELECT TOP in MySQL and Oracle is equivalent

MySQL syntax

SELECT column_name (s) from Table_namelimit number

Example

SELECT *from Personslimit 5

Oracle Syntax

SELECT column_name (s) from Table_namewhere Rownum<=number

Example

SELECT * from Personswhere rownum<=5

　　

The original persons table (used in the example)

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing
4	Obama	Barack	Pennsylvania Avenue	Washington

SQL TOP InstanceNow that we want to select the first two records from the Persons table above, we can use the following SELECT statement

SELECT TOP 2 * from Persons

　　

SQL TOP PERCENT Instances

Now we want to select 50% of the records from the Persons table above and use such SQL

SELECT TOP PERCENT * from Persons

　　

SQL Advanced Apps (top)