Recently, on this blog, I published an article on SQL SERVER – Interesting Observation – TOP 100 PERCENT and ORDER BY; this article was very well received because of the observation made in it. One of the comments suggested the workaround was to use clause WITH TIES along with TOP and ORDER BY. That is not the correct solution; however, but the same comment brings up the question regarding how WITH TIES clause actually works.
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.
Run the following script and you will find very interesting results.
USE AdventureWorks;
GO
-- Total Number of Rows in Table
SELECT COUNT(*) TotalRows
FROM Sales.SalesOrderDetail
GO
-- Total Count of Rows Grouped by ORDERQty
SELECT COUNT(*) Cnt, OrderQty
FROM Sales.SalesOrderDetail
GROUP BY OrderQty
ORDER BY OrderQty
GO
-- Example of Top 10 Records
SELECT TOP 10 *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty
GO
-- Example of Top 10 WITH TIES
SELECT TOP 10 WITH TIES *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty
GO
Now the above query brings back a very interesting result. When we have run the query for only 10 results, it brought back only 10 records and this was expected; however, when we ran it the same query with TIES, it brought back rows in thousands. In fact, it retrieved all the rows which are equivalent to the last rows of the previous result. The expected result is based on the column that is specified in ORDER BY. This implies that it will look for the column used in the ORDER BY to compare its equivalent in rest of the table with the last row of the results returned (I know this sentence is very complicated – what I have been trying to explain will be more clear from the image below).
From the example, it is very clear how WITH TIES works and what is the use for the same. This clause also works fine with TOP PERCENT syntax as well.
Reference: Pinal Dave (http://blog.sqlauthority.com)
pinaldave
Hi Pinal,
There is no difference in Execution plan
With Regards
Vijayakumar.
So wether you use WITH TIES depends on what you expect to get out of your data.
Hi Pinal,
Never used with ties clause, thanks for bringing out the feature/article and explanation.
In what real time scenario WITH TIES is used?
while fetching records, you are using the top keyword to fetch the records, say top 10 records and there is a eleventh record which matches the condition but due to top 10 condition that record has been dropped from the result set So in order to get that record you can use the keyword TIES.
hi,
while transaction can we need to allocate any memory?
thanks and regards
ravindra
When breaking a table into chunks using TOP WITH TIES, the obvious question is:
How do I capture (output) the last value of the sort column so that I can begin the next group at the next value?
I’m looking at the OUTPUT clause and the ranking functions today, but haven’t reached any conclusions yet.