Top n Clause in SQL Server

Back to: SQL Server Tutorial

Top n Clause in SQL Server with Examples

In this article, I am going to discuss the Top n Clause in SQL Server with examples. Please read our previous article where we discussed the Order By Clause in SQL Server with examples. As part of this article, we are going to discuss the following pointers related to the Top n Clause in SQL Server Database.

  1. What are Top n Clause and its use in SQL Server?
  2. Understand the Parameters or Arguments of Top n Clause in SQL Server.
  3. Examples of using the Top n Clause.
  4. How to use TOP PERCENT in SQL Server.
  5. Understanding TOP WITH TIES in SQL Server.
What are Top n Clause and its use in SQL Server?

The Top n Clause in SQL Server is used to specify the number of data rows to return. In large tables with thousands or millions of data rows, it takes more time to return all the records, which cause database performance issue. To fix this problem, we can return the specified number of data rows from a table using Top n Clause in SQL Server. The following is the syntax to use the TOP N CLAUSE in the 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:
  1. 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.
  2. 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.
  3. 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.
  4. Expressions: This is nothing but the columns or calculations that we want to retrieve.
  5. 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.
  6. 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.
  7. 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.
Example to understand TOP N CLAUSE in SQL Server:

Let us understand the use of the top n clause in SQL Server with examples. We are going to use the following Employee table to understand the TOP n clause in SQL Server.

Example to understand TOP N CLAUSE in SQL Server

 

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

-- Create Employee 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 data into Employee 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
Using TOP n Clause in SQL Server with Examples

In the below example, we are using the TOP(3) clause to selects the first 3 records from the Employee table where Gender is Male. If there are other records in the Employee table whose Gender is Male then those records 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, a total of 6 Employees are there whose Gender is male but in our example, as we are using the Top (3) clause it only returns the top 3 records from the Employee table order by ID ascending. So, when you will execute the above query, you will get the following output.

Using TOP n Clause in SQL Server with Examples

 

Using TOP PERCENT keyword in SQL Server:

Let us see how to use the TOP PERCENT keyword in SQL Server. The below example uses the TOP PERCENT keyword to select the first 70% of the records from the full result set. So in the below 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;

So, when you execute the above query, you will get the following output.

Using TOP PERCENT keyword in SQL Server

 

Using TOP with TIES in SQL Server:

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
Example:

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

Using TOP with TIES in SQL Server

Please use the 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

Select Query Using TOP with TIES Clause

WITH TIES using TOP N PERCENT

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

WITH TIES using TOP N PERCENT in SQL Server

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

Top n Clause in SQL Server Using Top

TOP n Clause with Update Statement: UPDATE TOP (3) Person SET Salary = 72000

TOP n Clause with Delete statement: DELETE TOP (2) FROM Person

In the next article, I am going to discuss the GROUP BY Clause in SQL Server with some examples. Here, in this article, I try to explain the TOP n Clause in SQL Server with Examples. I hope you understand the need and use of Top n Clause in SQL Server with Examples.