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

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.

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.

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

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.

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.

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

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.

Your current result is like below :

EmpNumber EmpName  Ranking
11        Maria    1
23        José     2
456       Pedro    3
456       Pedro    3 --WITH TIES

Probably your table is containing duplicate rows or may have historical ones as in general the EmpNumber is unique.

From Reference

Suppose we a have a table named myTable with below data:

ID NAME SALARY

1 Geeks 10000

4 Finch 10000

2 RR 6000

3 David 16000

5 Lesley 7000

6 Watson 10000

Query: SELECT * from myTable

order by salary desc

fetch first 3 rows only;

Output: We got only first 3 rows order by Salary in Descending Order

ID NAME SALARY

3 David 16000

1 Geeks 10000

4 Finch 10000

Note: In the above result we got first 3 rows, ordered by Salary in Descending Order, but we have one more row with same salary i.e, the row with name Watson and Salary 10000, but it didn’t came up, because we restricted our output to first three 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.

Real Life Example – Suppose we have 10 Racers running, and we have only 3 prizes i.e, first, second, third, but suppose, Racers 3 and 4 finished the race together in same time, so in this case we have a tie between 3 and 4 and that’s why both are holder of Position 3.

The WITH TIES option is important to the TOP() predicate. It enables the last place to include multiple rows if those rows have equal values in the columns used in the ORDER BY clause.

enter image description here

enter image description here

As you see in the first image , the last row has more identical values in the db .. then in the next picture the with ties get all the rows identical with the same value in the column selected in the "order by"

SELECT * from myTable order by rank desc fetch first 3 rows With Ties;

gives

ID    NAME       Rank
--------------------------
3    Dhoni     1
1    Geeks     2
6    Watson    3     **// We get Tied Row also**
4    Finch     3