Top Clause in SQL Server with Examples

Posted June 2, 2015 by Vishwanath Dalvi in Database, SQL Server

feature-sql1

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;


SQL_Server_Top_Clause_Screenshot1

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.

SQL_Server_Top_Clause_Screenshot2

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


SQL_Server_Top_Clause_With_Ties_Screenshot3

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.

SQL_Server_Top_Clause_Delete

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.