Top n Clause in SQL Server

Top n Clause in SQL Server

In this article, I am going to discuss the Top n Clause in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed the Order By Clause in SQL Server with examples. The Top n Clause in SQL Server is used to fetch the top n records from the table.

The Top n Clause in SQL Server is used to retrieve the records from one or more database tables and then limit the number of records returned based on a given value or percentage.

The syntax of Top n Clause in SQL Server
SELECT TOP (top_value) [PERCENT] [WITH TIES]
Expressions
FROM Tables
[WHERE Conditions]
[ORDER BY Expression [ASC | DESC]];
Parameters or Arguments of SQL Server Top n Clause

TOP (top_value): It Returns the top n number of records in the result set based on top_value. For example, TOP(10) in the select query will return the top 10 records from the full result set.

PERCENT: It is optional. If we specified the PERCENT, then the top rows are returned based on the percentage of the total result set. For example, if we specify TOP (10) PERCENT in the select query then it will return the top 10% of the records from the full result set.

WITH TIES: It is optional. If we specify the “WITH TIES” clause, then rows tied in the last place within the limited result set are returned.

Expressions: This is nothing but the columns or calculations that we want to retrieve.

Tables: The table names from which you want to retrieve the data. There should be at least one table specified in the FROM clause on the query.

WHERE conditions: It is Optional. If you want to retrieve the data based on some conditions then you need to specify the conditions using this where clause. 

ORDER BY: It is also optional. If you want to retrieve the top records either from ascending or descending order of certain column(s) then you need to use this Order By Clause.

Let us understand the use of the top n clause in SQL Server with some examples.

Create Database Tables

We are going to use the following Employee table to understand the TOP n clause in SQL Server.

Top n Clause in SQL Server

Please use below SQL Script to create and populate the Employee table with the required data.

-- Create Person table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  Gender VARCHAR(100),
  Department VARCHAR(100),
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO
--Insert some test data into Person table
INSERT INTO Employee VALUES('PRANAYA','PRANAYA@G.COM','Male', 'IT', 25000, 30,'MUMBAI')
INSERT INTO Employee VALUES('TARUN','TARUN@G.COM','Male', 'Payroll', 30000, 27,'ODISHA')
INSERT INTO Employee VALUES('PRIYANKA','PRIYANKA@G.COM','Female', 'IT', 27000, 25,'BANGALORE')
INSERT INTO Employee VALUES('PREETY','PREETY@G.COM','Female', 'HR', 35000, 26,'BANGALORE')
INSERT INTO Employee VALUES('RAMESH','RAMESH@G.COM','Male','IT', 26000, 27,'MUMBAI')
INSERT INTO Employee VALUES('PRAMOD','PRAMOD@G.COM','Male','HR', 29000, 28,'ODISHA')
INSERT INTO Employee VALUES('ANURAG','ANURAG@G.COM','Male', 'Payroll', 27000, 26,'ODISHA')
INSERT INTO Employee VALUES('HINA','HINA@G.COM','Female','HR', 26000, 30,'MUMBAI')
INSERT INTO Employee VALUES('SAMBIT','HINA@G.COM','Male','Payroll', 30000, 25,'ODISHA')
GO
Example – Using the TOP n Clause in SQL Server

In the below SQL Server SELECT TOP example, it selects the first 3 records from the Employee table where Gender is Male. If there are other records in the Employee table that Gender as Male then they will not be returned by the SELECT statement.

SELECT TOP(3)
ID, Name, EmailID, Gender, CITY, Department
FROM Employee
WHERE Gender = 'Male'
ORDER BY ID;

In our Employee Table total 6 Employees are there whose Gender is male but in our example, as we are using Top (3) clause it only returns top 3 records from the Employee table order by ID ascending as shown below.

Top n Clause in SQL Server Using Top

Example – Using TOP PERCENT keyword

The below SQL Server SELECT TOP example will select the first 70% of the records from the full result set. So in this example, the SELECT statement would return the top 70% of the records from the Employee table where the Gender is Male. The other 30% of the result set would not be returned by the SELECT statement.

SELECT TOP (70) PERCENT
ID, Name, EmailID, Gender, CITY, Department
FROM Employee
WHERE Gender = 'Male'
ORDER BY ID;

Top n Clause in SQL Server Using Top Percent

TOP WITH TIES

The TOP WITH TIES clause would include rows that may be tied in the last place within the limited result set. So let us understand the TOP WITH TIES Clause in SQL Server with an example.

The TOP WITH TIES can be used only with the following clause:

  1. Select Statement
  2. Order by clause is necessary for using this clause
  3. PERCENT clause
Creating a table in SQL Server to understand this concept

We are going to use the following Person table to understand this concept.

Top n Clause in SQL Server Using Top with ties

Please use below SQL Script to create and populate the Person table with the required test data.

CREATE TABLE Person
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(50),
  Salary int
)
GO

INSERT INTO Person VALUES('PRANAYA', 20000)
INSERT INTO Person VALUES('KUMAR', 30000)
INSERT INTO Person VALUES('ROUT', 25000)
INSERT INTO Person VALUES('PRANAYA', 25000)
INSERT INTO Person VALUES('KUMAR', 30000)
INSERT INTO Person VALUES('ROUT', 25000)
INSERT INTO Person VALUES('PRIYANKA', 20000)
INSERT INTO Person VALUES('PREETY', 30000)
GO

As we already discussed the Select TOP N query always returns exactly N number of records. The following example will return the TOP 3 records from the Person table.

SELECT TOP (3) Name FROM Person
ORDER BY Name

Top n Clause in SQL Server Using Top

Problem

In the above example, we have a situation in which the top clause returns exactly N number records and drops the records which have the same value as the last record fetched in the result set.

In the above Person table we have two employees with the name PRANAYA but both the Name PRANAYA will not be in the result since they are ignored by the TOP clause.

Select Query Using TOP WITH TIES Clause

SELECT TOP (3) WITH TIES Name FROM Person ORDER BY Name ASC

Top n Clause in SQL Server Using Top

WITH TIES using TOP N PERCENT

SELECT TOP (30) PERCENT Name FROM Person ORDER BY Name ASC

Top n Clause in SQL Server Using Top

SELECT TOP (30) PERCENT WITH TIES Name FROM Person ORDER BY Name ASC

Top n Clause in SQL Server Using Top

TOP Clause with Update Statement:

UPDATE TOP (3) Person SET Salary = 72000

TOP Clause with Delete statement:

DELETE TOP (2) FROM Person

In the next article, I am going to discuss GROUP BY Clause in SQL Server.

SUMMARY

In this article, I try to explain the TOP n clause in SQL Server steps by step with some simple examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *