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

Question

SELECT TOP 5 WITH TIES EmpNumber,EmpName 
FROM Employee 
Order By EmpNumber DESC

This above query return more than five result, What is the use of "With Ties" keyword 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.

Solution 4

For easier understanding, let's explain with a simple example on Northwind DB. Assuming you require products for the same price.

select 
     UnitPrice
    ,count(UnitPrice) as PriceCount
from Products
group by
    UnitPrice
order by 2 desc

]

You are now aware that there are currently 4 products whose price is 10 for example., but you will still get 2 rows -as many results as you sent with top clause-.

select top 2 UnitPrice,* from Products where UnitPrice=10

]

You have to submit the query with with ties clause for all matching rows. Even though you sent top 2, you will get more matching lines.

select top 2 with ties UnitPrice,* from Products where UnitPrice=10 order by 1

]

https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15#arguments

Returns two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.

You can specify the TOP clause with the WITH TIES argument only in SELECT statements, and only if you've also specified the ORDER BY clause. The returned order of tying records is arbitrary. ORDER BY doesn't affect this rule.

Solution 5

With my understanding of WITH TIES clause, if you want to [duplicate values], which the column specified in the Order By.

View more solutions

Comments

[Admin]

Thanks,Now I can see the difference. It mainly depends on the last row of the result set. If the last row is repeated then it will generate more rows. You made it more clear with the example.

[StevenMcD]

Interesting, after 13 years of developing I'd never seen this and needed to find something like this today. Thank you!

[giannis christofakis]

Is there a similar keyword to discard the siblings if any ? In our case to return 4 rows.

[Ivan Yuriev]

why this answer is not a top one, it's absolutely clear and simple!