SQL TOP Clause

From http://www.w3schools.com (Copyright Refsnes Data)

# SQL TOP Clause

## The TOP Clause

The TOP clause is used to specify the number of records to return.

The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Note: Not all database systems support the TOP clause.

### SQL Server Syntax

    SELECT TOP number|percent column_name(s)
    FROM table_name

## SQL SELECT TOP Equivalent in MySQL and Oracle

### MySQL Syntax

    SELECT column_name(s)
    FROM table_name
    LIMIT number

### Example

    SELECT *
    FROM Persons
    LIMIT 5

### Oracle Syntax

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

### Example

    SELECT *
    FROM Persons
    WHERE ROWNUM <= 5

## SQL TOP Example

The "Persons" table:

P_Id | LastName | FirstName | Address | City
--- | --- | --- | --- | ---
1 | Hansen | Ola | Timoteivn 10 | Sandnes
2 | Svendson | Tove | Borgvn 23 | Sandnes
3 | Pettersen | Kari | Storgt 20 | Stavanger
4 | Nilsen | Tom | Vingvn 23 | Stavanger

Now we want to select only the two first records in the table above.

We use the following SELECT statement:

    SELECT TOP 2 * FROM Persons

The result-set will look like this:

P_Id | LastName | FirstName | Address | City
--- | --- | --- | --- | ---
1 | Hansen | Ola | Timoteivn 10 | Sandnes
2 | Svendson | Tove | Borgvn 23 | Sandnes

## SQL TOP PERCENT Example

The "Persons" table:

P_Id | LastName | FirstName | Address | City
--- | --- | --- | --- | ---
1 | Hansen | Ola | Timoteivn 10 | Sandnes
2 | Svendson | Tove | Borgvn 23 | Sandnes
3 | Pettersen | Kari | Storgt 20 | Stavanger
4 | Nilsen | Tom | Vingvn 23 | Stavanger

Now we want to select only 50% of the records in the table above.

We use the following SELECT statement:

    SELECT TOP 50 PERCENT * FROM Persons

The result-set will look like this:

P_Id | LastName | FirstName | Address | City
--- | --- | --- | --- | ---
1 | Hansen | Ola | Timoteivn 10 | Sandnes
2 | Svendson | Tove | Borgvn 23 | Sandnes

From http://www.w3schools.com (Copyright Refsnes Data)