WITH TIES allows you to return more rows with values that match the last row in the limited result set. Note that
WITH TIES may cause more rows to be returned than you specify in the expression.
[dbo].[Products]( [Id] [ ] ( , ) , [ProductName] [nvarchar]( ) , [Price] [ ] ) GO
[dbo].[Products] ( , ) [dbo].[Products] ( , ) [dbo].[Products] ( , ) [dbo].[Products] ( , ) [dbo].[Products] ( , ) [dbo].[Products] ( , ) GO
TOP TIES ProductName, Price Products Price
Note: In the above result we got first 4 rows, ordered by Prince in Descending Order, but we have one more row with same price i.e, the row with name Bicycle 6 and Price 267.9, but it didn’t came up, because we restricted our output to first four rows only. But this is not optimal, because most of the time in live applications we will be required to display the tied rows also.
So, to overcome the above problem, SQL Server introduces a clause known as With Ties clause. Now, let’s see our previous example using With Ties clause.