Top Clause in SQL Server with Examples

Most of the time in real life, we try to find the top three scores in a class, the top five runners in a marathon, or the top 10 goals of the month. SQL server has a feature to select the TOP n records from a table.

We can retrieve the TOP n records from a table without using a WHERE clause. TOP can also be used with DML statements such as Update and Delete. Most of the time, TOP is used with an Order by clause to sort the results first in ascending or descending order and then to fetch the TOP n records. An order by clause with TOP makes sure we have sorted the data from a table.

TOP is used as a row limiter in SQL server just like LIMIT in Mysql.

Let’s take a look at an example of TOP used in a table.

Create Table SSCResults
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100),
Score NUMERIC(18,2)
);

INSERT INTO SSCResults VALUES ('Shailesh A',98.0);
INSERT INTO SSCResults VALUES ('Atul K',90.0);
INSERT INTO SSCResults VALUES ('Vishal P',89.0);
INSERT INTO SSCResults VALUES ('Naryan N',88.0);
INSERT INTO SSCResults VALUES ('Rohit G',88.0);
INSERT INTO SSCResults VALUES ('Varsha K',85.0);
INSERT INTO SSCResults VALUES ('Sangram K',83.0);
INSERT INTO SSCResults VALUES ('Vish K',79.0);

SELECT * FROM SSCResults;

Example 1 – Selecting TOP n Records in SQL Server: Find the top three scorers in a SSCResults table

A quick way to find this is to sort the Score column in descending order and select the top three records.

SELECT TOP 3 *
FROM   SSCResults
ORDER  BY Score  DESC

The query above has sorted the Score field from the highest score to the lowest score first, and then it has selected the top three scores. SELECT * indicates we want to retrieve all the columns from the SSCResults table.

Example 2 – Top with Ties: Dealing with tied values

When we query the SSCResults table, we see Id = 4 and Id = 5 have the same score. In this case, if I fetch the top four records from the SSCResults table based on the Score column, Id = 5 would not show up in the list because the Top 4 records condition is not set up to handle a tie scenario.

Let’s query the top four records from the SSCResults table.

SELECT TOP 4 *
FROM SSCResults

We did not see that Id = 5 has the same score as Id = 4 in the results above because we did not handle a tie scenario. To get the Id = 5 record, we need to use TOP with TIES.

SELECT TOP 4 WITh TIES *
FROM   SSCResults
ORDER  BY Score  DESC

Top Clause with Update and Delete Statements

Example 3 – Updating Top 3 Records in a Table: Update the top three records in a table

Let’s update the score of the top 3 scorers by 0.5 percent.

We cannot use Order by directly with an Update statement. We need to use a subquery to select the top three records and then update.

update  SSCResults
set    Score = Score + 0.5    
where  ID in (select top 3 ID
            from  SSCResults
            order by score desc);

The query above will execute the subquery first to select the top three ids (top three scorers), and then it will update their scores by adding 0.5 percent.

select *
from  SSCResults
order by score desc

We can see that the top three scores’ percents have been updated by 0.5 percent.

Example 4 – Deleting the Top 3 Records in a Table: Delete the three lowest scores from the table

DELETE FROM SSCResults
where  ID in (select top 3 ID
            from  SSCResults
            order by score ASC);

First, the subquery will fetch the lowest scores from the table SSCResults, and an outer query will delete these records based on the ID produced by the subquery.