How to use Top with Ties in SQL Server - SQL Server / TSQL Tutorial Part 114

In last post, we learnt how to return top / bottom x rows from sql server table. In this post, we will learn Top with ties.

According to BOL
 "WITH TIES
Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows."

Let's create dbo.Customer table and find out the difference between Top and Top With Ties.

Create table dbo.Customer(
Id int,
Name VARCHAR(10),
Address VARCHAR(100))

--Insert records in dbo.Customer table
Insert into dbo.Customer
Values(1,'Aamir','XyZ Address')
,(2,'Raza',Null)
,(1,'Aamir','XyZ Address')
,(1,'John','XyZ Address')
,(5,'Lisa','XyZ Address')

Noticed that we have duplicate records as high lighted above. Let's run the below queries, first with only Top and second with "With Ties" and observe the output.

Select top (2) * From dbo.Customer 
order by ID
how to use Top x to get top records from table in SQL Server - TSQL Tutorial
Select top (2) WITH TIES  * From dbo.Customer 
order by ID

How to use Top with Ties in SQL Server to get Top x rows - SQL Server / TSQL Tutorial

The first query returned only 2 records but second query with "With Ties" returned three records as the value for Id for third record was also 1 that matched with second row ( id column value=1). If you use "With Ties" with Top, the query will also give you all ties with the last row based on the ordering column.