SQL With Ties Clause

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

The selection of which the rows to return is nondeterministic.
This means that if you run the query again, without the underlying data changing, theoretically you could get a different set of three rows.
In practice, the row selection will depend on physical conditions like :

• optimization choices
• storage engine choices
• data layout
• etc...

If you actually run the query multiple times, as long as those physical conditions don’t change, there’s some likelihood you will keep getting the same results. But it is critical to understand the “physical data independence” principle from the relational model, and remember that at the logical level you do not have a guarantee for repeatable results. Without ordering specification, you should consider the order as being arbitrary, resulting in a nondeterministic row selection

If you want to use TOP WITH TIES you must use order by.

Create Table

CREATE TABLE [dbo].[Products](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](50) NULL,
[Price] [float] NULL) 
GO

The following illustrates the INSERT statement that inserts rows into an existing table

INSERT INTO [dbo].[Products] VALUES ('Bicycle 1' , 258.2)
INSERT INTO [dbo].[Products] VALUES ('Bicycle 2' , 265.3)
INSERT INTO [dbo].[Products] VALUES ('Bicycle 3' , 267.8)
INSERT INTO [dbo].[Products] VALUES ('Bicycle 4' , 268.9)
INSERT INTO [dbo].[Products] VALUES ('Bicycle 5' , 267.9)
INSERT INTO [dbo].[Products] VALUES ('Bicycle 6' , 267.9)
GO

then

SELECT TOP 4 WITH TIES ProductName, Price
FROM Products  ORDER BY Price

In this example, the two expensive product has a list price of 267.9. Because the statement used TOP WITH TIES, it returned one more products whose list prices are the same as the forth one.

If you run the normal top 4 then the result set will be 

select top 4 ProductName, Price FROM Products order by 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.