TOP(n) WITH TIES

Most SQL developers are familiar with using TOP(n) to return a desired number of results, but what if our ORDER BY column ends up having matching results, do we want to see them? If so, we can use WITH TIES in conjunction with TOP(n).

“WITH TIES
Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows.” -Microsoft

Consider this scenario. What if we wanted to grab the TOP 10 employees with the highest pay rate? It wouldn’t be uncommon for there to be matching pay rates.

For this example, the estimated cost and execution plans match, but with that said, you should always test your code for performance.

Here is the code so you can give it a try yourself.

USE AdventureWorks2012
GO

SELECT TOP (10) p.BusinessEntityID
	,e.LoginID
	,p.rate
FROM [HumanResources].[EmployeePayHistory] p
INNER JOIN [HumanResources].[Employee] e ON e.BusinessEntityID = p.BusinessEntityID
WHERE 1 = 1
ORDER BY p.rate DESC

SELECT TOP (10)
WITH TIES
p.BusinessEntityID
	,e.LoginID
	,p.rate
FROM [HumanResources].[EmployeePayHistory] p
INNER JOIN [HumanResources].[Employee] e ON e.BusinessEntityID = p.BusinessEntityID
WHERE 1 = 1
ORDER BY p.rate DESC