SQL Server: Tips and Tricks - 2 (2017)
How to use Top with Ties in SQL Server
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
--Insert records in dbo.Customer table
Insert into dbo.Customer
Noticed that we have duplicate records as highlighted 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
Select top (2) WITH TIES * From dbo.Customer
order by ID
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