TOP WITH TIES in SQL Server

Normally we use TOP keyword to get the TOP n rows from the table. For example you have a table which contains employees’ details with salary and you wanted to know the top 5 salary earning employees details.

If you run a query like

select top 5 salary, Name  from [dbo].[Employee]

You will get top 5 employee details but what will happen if you have same salary for 3 employees.  In this case you may use WITH TIES along with TOP keyword.

Let’s see a simple example by creating the table and running a query against the table.

First we will create a simple Employee table.

CREATE TABLE [dbo].[Employee](

      [EmpID] [int] NULL,

      [Name] [varchar](50) NULL,

      [Salary] [bigint] NULL

) ON [PRIMARY]

Now we will insert 10 rows in the table,

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (1,'Asheej',10)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (2,'Santosh',20)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (3,'Priya',30)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (4,'Yathish',40)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (5,'Mayur',40)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (6,'Ryan',50)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (7,'Karolina',40)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (8,'Pravas',60)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (9,'Harkirat',70)

INSERT INTO [dbo].[Employee] ([EmpID],[Name],[Salary])VALUES (10,'Irmina',20)

GO

We have the table ready, now it’s the time to run the query.

First we will run the simple SQL query with TOP keyword

select top 5 salary, Name  from [dbo].[Employee]

Out put of this query will be

salary   Name

10        Asheej

20        Santosh

30        Priya

40        Yathish

40        Mayur

You can see that it returned 5 rows but it didn’t show all the employees details who is earning the same salary.

Now we are going to run the actual query we supposed to use in this case,

select top 5 WITH TIES salary, Name from [dbo].[Employee] order by Salary

salary    Name

10           Asheej

20           Santosh

20           Irmina

30           Priya

40           Yathish

40           Mayur

40           Karolina

One thing you have to keep in your mind is that along with WITH TIES you always have to use ORDER BY.

If you are looking for some details about this it is always better to refer MSDN