TOP clause is commonly used to get the top required rows from a result set. The beauty of this clause is that it can be used with the
WITH TIES clause to retrieve all similar rows to a base result set.
According to BOL “WITH TIES specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified. “
Using the Code
For example, from the following simple table, I need to get records which have a minimum purchase date value. In the first method, we will use the common
CREATE TABLE #MyTable (Purchase_Date DATETIME, Amount INT)
INSERT INTO #MyTable
SELECT '11/11/2011', 100 UNION ALL
SELECT '11/12/2011', 110 UNION ALL
SELECT '11/13/2011', 120 UNION ALL
SELECT '11/14/2011', 130 UNION ALL
SELECT '11/11/2011', 150
SELECT * FROM #MyTable
WHERE Purchase_Date IN
(SELECT MIN(Purchase_Date) FROM #MyTable)
We can also get our desired results by using
SELECT TOP(1) WITH TIES * FROM #MyTable
ORDER BY Purchase_Date
By executing the above query, you can find
TOP WITH TIES worked amazingly, but is this short code really smart code? Let’s compare their performances.
TOP…WITH TIES clause really shortened our code, you can see that it performed poorly as compared to our traditional code. This happened just because of the
ORDER BY clause. This poor performance can be controlled by placing a well defined index.