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
FROM table_name
SQL SELECT TOP Equivalent in MySQL and Oracle
MySQL Syntax
SELECT column_name(s)
FROM table_name
LIMIT number
FROM table_name
LIMIT number
Example
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
EXAMPLE:
SELECT *
FROM Persons
WHERE ROWNUM <=5
FROM Persons
WHERE ROWNUM <=5
SQL TOP Example
The "Persons" table:
| 1 | ADNAN | AD | Timoteivn 10 | Sandnes |
| 2 | UMAIR | UMI | Borgvn 23 | Sandnes |
| 3 | ASAD | ASD | Storgt 20 | Stavanger |
| 4 | ALI | PAPI | 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:
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
SQL TOP PERCENT Example
The "Persons" table:
| 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:
| 1 | UMAIR | UMI | Timoteivn 10 | Sandnes |
| 2 | ALI | pApi | Borgvn 23 | Sandnes |
0 comments:
Post a Comment