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

Question: How Does TOP WITH TIES Work in SQL Server?

Answer: This is a very old question I just encountered in the recent Comprehensive Database Performance Health Check.

Let us first understand how TOP WITH TIES works in simple words.

First of all, the clause WITH TIES can be used only with TOP and ORDER BY, both the clauses are required. Let us understand from one simple example how this clause actually works. Suppose we have 100 rows in the table and out of that 50 rows have same value in column which is used in ORDER BY; when you use TOP 10 rows, it will return you only 10 rows, but if you use TOP 10 WITH TIES, it will return you all the rows that have same value as that of the last record of top 10 — which means a total of 50 records.

Let us understand with a simple example:

Run following example where we will see each count of the items with OrderQty.

-- Total Count of Rows Grouped by ORDERQty
SELECT COUNT(*) Cnt, OrderQty
FROM Sales.SalesOrderDetail
GROUP BY OrderQty
ORDER BY OrderQty
GO

Here you will see that we have 74954 items with OrderQty 1 and 7394 items with OrderQty 4.

Next, we will run the following query with the keyword TOP 10, it will bring us following resultset with 10 rows.

-- Example of Top 10 Records
SELECT TOP 10 *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 1
ORDER BY OrderQty
GO

Next, we will run the same command and we will have additional keyword TOP 10 with Ties.

-- Example of Top 10 WITH TIES
SELECT TOP 10 WITH TIES *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 1
ORDER BY OrderQty
GO

This time instead of just returning only 10 rows, it will return all the rows where OrderQty = 1. This is because when we use TOP WITH TIES it returns all the rows where the OrderQty is 10.

I hope from this example it is clear how TOP works and how it’s behavior changes when we use TOP WITH TIES.

Reference: Pinal Dave (https://blog.sqlauthority.com)