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.

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