How Does TOP WITH TIES Work in SQL Server? – Interview Question of the Week #159

Question: Why can TOP (10) WITH TIES return far more than ten rows?

Answer: It includes all rows tied with the last selected row on the complete ORDER BY key. This old interview question came up again during a database performance health check. The phrase with ties means that SQL Server does not cut a group of equal sort values in half at the boundary. It keeps every row whose ORDER BY value ties with the last selected row.

Imagine 100 rows, 50 of which share the highest sort value. TOP (10) returns ten of those rows, but TOP (10) WITH TIES returns all 50 because the tenth row has the same sort value as the other 40. That conclusion depends on how you sort; it is not a rule that every use of WITH TIES returns 50 rows.

The following small example makes the boundary visible without a sample database. Two rows share the second-highest score, so asking for the top two with ties returns three.

WITH Scores AS
(
    SELECT v.Item, v.Score
    FROM (VALUES (N'A', 9), (N'B', 8), (N'C', 8),
                 (N'D', 7), (N'E', 6)) AS v(Item, Score)
)
SELECT TOP (2) WITH TIES Item, Score
FROM Scores
ORDER BY Score DESC;
-- A (9), B (8), and C (8). The two rows at 8 tie.

My original AdventureWorks example is a much larger version of the same effect. The saved count result shows 74,954 SalesOrderDetail rows with OrderQty = 1. Those rows all have the same ordering value:

SELECT OrderQty, COUNT(*) AS Cnt
FROM Sales.SalesOrderDetail
GROUP BY OrderQty
ORDER BY OrderQty;

Now filter to that quantity and sort only by OrderQty. Ordinary TOP (10) returns ten arbitrary members of the equal-valued set. TOP (10) WITH TIES returns every row in the set because all 74,954 rows tie at the cutoff.

SELECT TOP (10) SalesOrderID, SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE OrderQty = 1
ORDER BY OrderQty;

SELECT TOP (10) WITH TIES
       SalesOrderID, SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE OrderQty = 1
ORDER BY OrderQty;

If you add a unique column such as SalesOrderDetailID to the ORDER BY, these rows no longer tie on the full ordering key. Use that when you need a repeatable ten-row result. Use WITH TIES when the business rule really asks for all records sharing the boundary value.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.