Click here to Skip to main content
15,867,838 members
Articles / Database Development / SQL Server / SQL Server 2012
Tip/Trick

SQL Server: TOP…WITH TIES - A Beauty of TSQL

Rate me:
Please Sign up or sign in to vote.
4.38/5 (8 votes)
19 Apr 2013CPOL1 min read 87.2K   12   5
The 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.

Introduction

The 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.

Book Online

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 IN clause.

SQL
--Create temporary table
CREATE TABLE #MyTable (Purchase_Date DATETIME, Amount INT)
--Insert few rows to hold
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
--Get all records which has minimum purchase date (i.e. 11/11/2011)
SELECT * FROM #MyTable
WHERE Purchase_Date IN

       (SELECT MIN(Purchase_Date) FROM #MyTable)

Image 1

We can also get our desired results by using TOP…WITH TIES.

SQL
SELECT TOP(1) WITH TIES * FROM #MyTable
ORDER BY Purchase_Date

Performance

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.

Image 2

Though the 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.

Image 3

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.

Comments and Discussions

 
GeneralMy vote of 5 Pin
npdev1321-Apr-13 18:13
npdev1321-Apr-13 18:13 
QuestionExecution Plan Pin
Juzer23-Oct-12 3:19
Juzer23-Oct-12 3:19 
AnswerRe: Execution Plan Pin
aasim abdullah23-Oct-12 8:00
aasim abdullah23-Oct-12 8:00 
GeneralRe: Execution Plan Pin
Juzer23-Oct-12 13:31
Juzer23-Oct-12 13:31 
GeneralMy vote of 5 Pin
Savalia Manoj M22-Oct-12 20:52
Savalia Manoj M22-Oct-12 20:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.