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

(

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

https://3.bp.blogspot.com/-FsN2QNh5mPc/V2Fv9sRKwjI/AAAAAAAAfNc/D2r_bZcuRlITvoLhNXnyTItxGjvEfZr1gCLcB/s320/Capture.PNG

Select top (2) WITH TIES * From dbo.Customer

order by ID

https://1.bp.blogspot.com/-ivuvYRn3wgA/V2FwD9GeV_I/AAAAAAAAfNk/W5jaP4jXEf41jgtrPP4TfPdejFfRXjxdgCLcB/s320/Capture.PNG

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