Have you ever wondered how to retrieve all the rows that have the same value as the last record in a result set? In SQL Server, the WITH TIES clause can help you achieve this. In this article, we will explore the functionality of the WITH TIES clause and how it can be used in conjunction with the TOP and ORDER BY clauses.

First, let’s clarify that the WITH TIES clause can only be used with the TOP and ORDER BY clauses. Both of these clauses are required for the WITH TIES clause to work properly.

Let’s consider a simple example to understand how the WITH TIES clause actually works. Suppose we have a table with 100 rows, and out of those 100 rows, 50 rows have the same value in a specific column that we are using in the ORDER BY clause. If we use the TOP 10 clause, it will return only 10 rows. However, if we use the TOP 10 WITH TIES clause, it will return all the rows that have the same value as the last record of the top 10. In this case, it will return a total of 50 records.

Let’s run the following script to see the results:

USE AdventureWorks;

-- Total Number of Rows in Table
SELECT COUNT(*) TotalRows FROM Sales.SalesOrderDetail;

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

-- Example of Top 10 Records
SELECT TOP 10 * FROM Sales.SalesOrderDetail
ORDER BY OrderQty;

-- Example of Top 10 WITH TIES
SELECT TOP 10 WITH TIES * FROM Sales.SalesOrderDetail
ORDER BY OrderQty;

The above query will bring back some interesting results. When we run the query for only 10 results, it will return only 10 records, as expected. However, when we run the same query with the WITH TIES clause, it will retrieve all the rows that are equivalent to the last rows of the previous result. The comparison is based on the column specified in the ORDER BY clause. This means that it will compare the equivalent column values in the rest of the table with the last row of the results returned.

From the example, it is clear how the WITH TIES clause works and its usefulness. This clause can also be used with the TOP PERCENT syntax.

Next time you need to retrieve all the rows that have the same value as the last record in a result set, remember the power of the WITH TIES clause in SQL Server.