SQL Advanced Tutorial-top clause

TOP clause

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.

Note: Not all database systems support the TOP clause.

Syntax for SQL Server:

SELECT TOP number|percent column_name (s) from table_name

SQL SELECT TOP in MySQL and Oracle is the 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 table (used in the example):

Persons table:

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 Instance

Now, we want to select the first two records from the "Persons" table above.

We can use the following SELECT statement:

TOP 2* FROM Persons

Results: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York

SQL TOP PERCENT Instances

Now, we want to select 50% of the records from the "Persons" table above.

We can use the following SELECT statement:

TOP 50 PERCENT* FROM Persons

Results:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York

SQL Advanced Tutorial-top clause