SELECT TOP 10 SQL Examples

Problem

What is the SQL TOP clause and how do I use it in a SQL database?

Solution

We’ll look at several examples of the SELECT TOP statement in the following sections of this SQL tutorial:

Why TOP is Helpful and How is it Used
Number of Rows vs. Percentage of Rows
SELECT and DELETE
ORDER BY, GROUP BY, HAVING
TIES

SQL SELECT TOP Syntax

The following examples were run in SQL Server Management Studio (SSMS) 19.2 against a copy of the Wide World Importers sample database on a Microsoft SQL Server 2022 server.

The SQL TOP clause is used with the SELECT statement to specify the number of rows to return from a query result with the following syntax:

[   
    TOP (expression) [PERCENT] 
    [ WITH TIES ] 
]

Why is SQL SELECT TOP Helpful, and How is it Used?

The TOP clause limits the number of records returned by a query. So, it’s faster. It can also be useful if you’re developing queries where you’re only interested in the query’s logic as you’re developing them instead of returning all the data. TOP is usually used with the ORDER BY clause, as the order of the data is not guaranteed unless specified. TOP and ORDER BY can be used together to return the highest or lowest set of data, i.e., top x or top x percent of best or worst selling items with the ASC or DESC parameter.

Sample Dataset

Here is a SQL query that pulls all rows from table VehicleTemperatures ordered by RecordedWhen.

SELECT [VehicleTemperatureID]
     , [VehicleRegistration]
     , [ChillerSensorNumber]
     , [RecordedWhen]
     , [Temperature]
FROM [Warehouse].[VehicleTemperatures]
ORDER BY [RecordedWhen];
GO

All the records in the VehicleTemperatures table are returned, totaling 65,998.

SQL SELECT TOP 10 Rows vs. SQL SELECT TOP 10 PERCENT Rows

There are two ways to determine the number of rows returned by the TOP clause. We can specify either the number of rows or the percentage of the result set to return.

First, add TOP 10 to the SELECT in the previous SQL query.

SELECT TOP 10  [VehicleTemperatureID]
             , [VehicleRegistration]
             , [ChillerSensorNumber]
             , [RecordedWhen]
             , [Temperature]
FROM [Warehouse].[VehicleTemperatures]
ORDER BY [RecordedWhen];
GO

And we only get the first 10 records ordered by RecordedWhen.

By adding PERCENT after the TOP 10 in the SELECT statement, instead of getting 10 rows, we get 10 percent of the rows returned.

SELECT TOP 10 PERCENT [VehicleTemperatureID]
                    , [VehicleRegistration]
                    , [ChillerSensorNumber]
                    , [RecordedWhen]
                    , [Temperature]
FROM [Warehouse].[VehicleTemperatures]
ORDER BY [RecordedWhen];
GO

The total number of rows is 65,998. Following the math: 65,998 X 0.10 = 6,599.8, which is a float, so it’s rounded up to 6600 rows.

The default ORDER BY is ascending, so we get the oldest dates.

To get the newest dates, we can change the ORDER BY to DESC(ENDING).

SELECT TOP 10 [VehicleTemperatureID]
     , [VehicleRegistration]
     , [ChillerSensorNumber]
     , [RecordedWhen]
     , [Temperature]
FROM [Warehouse].[VehicleTemperatures]
ORDER BY [RecordedWhen] DESC;
GO

Here are the newest 10 recorded temperatures.

Now, let’s try PERCENT.

SELECT TOP 10 PERCENT [VehicleTemperatureID]
     , [VehicleRegistration]
     , [ChillerSensorNumber]
     , [RecordedWhen]
     , [Temperature]
FROM [Warehouse].[VehicleTemperatures]
ORDER BY [RecordedWhen] DESC;
GO

Here, we get the newest 10 percent of the recorded temperatures.

SQL SELECT TOP with DELETE

The TOP clause can be used in a subquery to determine records to delete.

This query gives us a list of the oldest 10 percent of RecordedWhen values:

SELECT TOP 10 PERCENT
    [RecordedWhen]
FROM [Warehouse].[VehicleTemperatures]
ORDER BY [RecordedWhen] DESC

Passing the list to this DELETE statement will delete the oldest 10 percent of the records.

DELETE [Warehouse].[VehicleTemperatures]
WHERE [RecordedWhen] IN (
                            SELECT TOP 10 PERCENT
                                [RecordedWhen]
                            FROM [Warehouse].[VehicleTemperatures]
                            ORDER BY [RecordedWhen]
                        );
GO

SQL SELECT TOP with ORDER BY, GROUP BY, HAVING

We’ve seen how the ORDER BY clause is used to sort records before limiting what’s returned with the TOP clause. Now, we’ll look at using the GROUP BY and HAVING clauses to group and filter the records returned.

Here, we’re taking the sum of the before-tax amount grouped by each supplier.

SELECT [SupplierID]
     , SUM([AmountExcludingTax]) AS [AmountExcludingTax]
FROM [Purchasing].[SupplierTransactions]
GROUP BY [SupplierID];
GO

Below are the supplier IDs and the sums of pretax amounts for each returned in no particular order.

If we’re only interested in amounts over 1000, add HAVING SUM([AmountExcludingTax]) > 1000; to limit the records returned to those where the sum is greater than 1000.

SELECT [SupplierID]
     , SUM([AmountExcludingTax]) AS [AmountExcludingTax]
FROM [Purchasing].[SupplierTransactions]
GROUP BY [SupplierID]
HAVING SUM([AmountExcludingTax]) > 1000;
GO

Supplier IDs and the sums of pretax amounts over 1000

Adding ORDER BY SUM([AmountExcludingTax]); orders the records by the sum of the pretax amount in ascending order.

SELECT [SupplierID]
     , SUM([AmountExcludingTax]) AS [AmountExcludingTax]
FROM [Purchasing].[SupplierTransactions]
GROUP BY [SupplierID]
HAVING SUM([AmountExcludingTax]) > 1000
ORDER BY SUM([AmountExcludingTax]);
GO

Supplier IDs and the sums of pretax amounts over 1000 in ascending order

Adding DESC to the end of ORDER BY SUM([AmountExcludingTax]); changes the sort order to descending.

SELECT [SupplierID]
     , SUM([AmountExcludingTax]) AS [AmountExcludingTax]
FROM [Purchasing].[SupplierTransactions]
GROUP BY [SupplierID]
HAVING SUM([AmountExcludingTax]) > 1000
ORDER BY SUM([AmountExcludingTax]) DESC;
GO

Supplier IDs and the sums of pretax amounts over 1000 in descending order

To return only the highest 50 percent of pretax amounts is achieved by adding a TOP 50 PERCENT to the SELECT statement.

SELECT TOP (50) PERCENT
    [SupplierID]
  , SUM([AmountExcludingTax]) AS [AmountExcludingTax]
FROM [Purchasing].[SupplierTransactions]
GROUP BY [SupplierID]
HAVING SUM([AmountExcludingTax]) > 1000
ORDER BY SUM([AmountExcludingTax]) DESC;
GO

We end up with the highest 50 percent of the total pretax amount and the associated supplier IDs of the records in the SupplierTransactions table that are over 1000.

Highest 50 percent of the total pretax amount and the associated supplier IDs of the records in the SupplierTransactions table that are over 1000

SQL SELECT TOP with TIES

In the event of a tie, the result set will include additional records beyond the last record of that tie, as specified in the ORDER BY clause.

In the event of a tie, additional records beyond the last record of that tie, as specified in the ORDER BY clause, will be included in the result set.

SELECT TOP 10 WITH TIES 
       [VehicleTemperatureID]  
     , [VehicleRegistration]
     , [ChillerSensorNumber]
     , [RecordedWhen]
     , [Temperature] 
FROM [Warehouse].[VehicleTemperatures]
ORDER BY [RecordedWhen];
GO

Here, we get 11 records because the last 3 records have the same RecordedWhen value, so all are included.

Next Steps

For more information, here are some additional tips on the TOP clause:

Resources

Community

Subscribe