SQL TOP

SQL TOP

The SQL TOP clause is used to specify the number of records returned by the query. With TOP you can specify to retrieve the first N records or the first X percent of records.

The TOP expression can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Suppose we have the following table:

Table Book_Information

Book_name    Cost   Date
 A           $500   Jan-05-1998
 B           $50    Jan-07-1997
 C           $30    Jan-08-1998
 D           $200   Jan-08-1999

We use

SELECT TOP 2 book_name, Cost, Date
FROM Book_Information
ORDER BY Cost DESC;

Result:

Book_name   Cost   Date
 A         $500    Jan-05-1998
 D         $200    Jan-08-1999

Alternatively, if we want to show the top 25% of Cost amounts from Table Book_Information, we key in,

SELECT TOP 25 PERCENT book_name, Cost, Date
FROM Book_Information
ORDER BY Cost DESC;

Result:

Book_name   Cost   Date
 A          $500   Jan-05-1998

SQL TOP accesses just the first N or X percent of records. Usually such queries are accompanied by ORDER BY clause.


The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order. TOP n returns n random rows. For example, the following INSERT statement contains the ORDER BY clause, yet this clause does not affect the rows directly referenced by the INSERT statement.

INSERT TOP (2) INTO Table2 (ColumnB)
SELECT ColumnA FROM Table1
ORDER BY ColumnA

The ORDER BY clause in the previous query references only the rows returned by the nested SELECT statement. The INSERT statement chooses any two rows returned by the SELECT statement. To make sure that the top two rows from the SELECT subquery are inserted, rewrite the query as follows.

INSERT INTO Table2 (ColumnB)
SELECT TOP (2) ColumnA FROM Table1
ORDER BY ColumnA

[Source of above Insert Statement]