SQL TOP

SQL > Advanced SQL > Top

In the previous section, we saw how LIMIT can be used to retrieve a subset of records in MySQL. In Microsoft SQL Server, this is accomplished using the TOP keyword.

The syntax for TOP is as follows:

SELECT TOP [TOP argument] "column_name"
FROM "table_name";

where [TOP argument] can be one of two possible types:

1. [N]: The first N records are returned.

2. [M] PERCENT: The number of records corresponding to M% of all qualifying records are returned.

For example, we may wish to show the two highest sales amounts in Table Store_Information,

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
San Francisco	300	Jan-08-1999
Boston	700	Jan-08-1999

we key in,

SELECT TOP 2 Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales DESC;

Result:

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
Boston	700	Jan-08-1999

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

SELECT TOP 25 PERCENT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales DESC;

Result:

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999