What is the use of WITH TIES keyword in SELECT statement in SQL Queries?

Solution 1:

From TOP (Transact-SQL)

Used when you want to return two or more rows that tie for last place in the limited results set.

Note the example

SQL Fiddle DEMO

We have a table with 6 entires 1 to 4 and 5 twice.

Running

SELECT TOP 5 WITH TIES *
FROM MyTable 
ORDER BY ID;

returns 6 rows, as the last row is tied (exists more than once.)

Where as

SELECT TOP 5 WITH TIES *
FROM MyTable 
ORDER BY ID DESC;

returns only 5 rows, as the last row (2 in this case) exists only once.

Solution 2:

Check this query and will be more clear.

SELECT TOP 5 WITH TIES *
FROM MyTable 
ORDER BY ID;

RESULT:- 1 2 3 4 5 5

SELECT TOP 5 *
FROM MyTable 
ORDER BY ID;

RESULT:- 1 2 3 4 5

Solution 3:

From here

Using TOP WITH TIES to include rows that match the values in the last row

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.