SQL SERVER – ORDER BY Clause and TOP WITH TIES

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)

25 thoughts on “SQL SERVER – ORDER BY Clause and TOP WITH TIES

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

      Like

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

    Like

  2. Hello Pinal,

    I have table with child and parent hierarchy within same table.
    I hvae column to identify the parent of the respective node.
    Records like

    Id name Parent
    1 ABC Null
    2 PQR 1
    3 DEF Null
    4 LMN Null
    5 OPQ Null
    6 JKL 2
    7 TUY 4

    So i want to display parent & then his child. if no child the next parent
    Output expected is

    Id name Parent
    1 ABC Null
    2 PQR 1
    6 JKL 2
    3 DEF Null
    4 LMN Null
    7 TUY 4
    5 OPQ Null

    Please give the query providing the expected output parent following expected output.

    Regards,
    Vishal Shinde.

    Like

    • Hi Vishal,

      Here is the solution –

      CREATE TABLE #Temp
      (Id INT,
      Name varchar(3),
      Parent INT)

      INSERT INTO #Temp VALUES (1,’ABC’,Null)
      INSERT INTO #Temp VALUES (2,’PQR’, 1)
      INSERT INTO #Temp VALUES (3,’DEF’, Null)
      INSERT INTO #Temp VALUES (4,’LMN’, Null)
      INSERT INTO #Temp VALUES (5,’OPQ’, Null)
      INSERT INTO #Temp VALUES (6,’JKL’, 2)
      INSERT INTO #Temp VALUES (7,’TUY’, 4)

      SELECT * FROM #Temp

      SELECT * FROM #Temp ORDER BY Coalesce(Parent,Id)

      Like

        • agreed isnull will give you same result as coalesce in above case, but coalesce will work with multiple column list, where else isnull will work with single column and there alternative value in case of null.

          Like

  3. Hello Pinal,

    As per the statement ( 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)

    SELECT TOP 74956 WITH TIES *
    FROM Sales.SalesOrderDetail
    ORDER BY OrderQty
    GO

    the last row of the results returned will be with the OrderQty=2, for which the equivalent is 14200 rows,

    The actual result set returned is 74954 + 14200 = 89154 records
    Can you please explain further.

    Thanks,

    Like

  4. Hi Pinal,
    I’m Viren from Mumbai.

    I want to write 1 complex order by clause, plz help.

    in Table A, there’s a column called ShowOnTop which contain either sort order like 1, 2, 3, … or 0 by default. Another column called Type_Id which contains only 1, 2, 3.
    I want to order my query such that first rows would be sorted according to ShowOnTop , den by Type_Id. But if in ShowOnTop, contains 0, den they shld be shown below Type_Id.

    Ex:
    Row ShowOnTop Type_Id
    1 2 1
    2 3 2
    3 0 2
    4 1 2
    5 0 1
    6 0 3
    7 0 1

    Data displayed would be:
    4
    1
    2
    5
    7
    3
    6

    Like

    • Hi Viren,

      Hope you solve the problem. If not, here is the solution –

      CREATE TABLE #TEMP
      (ShowOnTop INT,
      TypeId INT)

      INSERT INTO #TEMP VALUES (2,1)
      INSERT INTO #TEMP VALUES (3,2)
      INSERT INTO #TEMP VALUES (0,2)
      INSERT INTO #TEMP VALUES (1,2)
      INSERT INTO #TEMP VALUES (0,1)
      INSERT INTO #TEMP VALUES (0,3)
      INSERT INTO #TEMP VALUES (0,1)

      SELECT * FROM #TEMP

      SELECT * FROM #TEMP
      ORDER BY CASE WHEN ShowOnTop = 0 THEN 999999999 ELSE ShowOnTop END, TypeId

      Put maximum number in CASE statement. Here I put 999999999.

      Try this.

      Cheers

      Shekhar Teke
      Sr DBA
      Talkingtech Limited, NZ

      Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  6. I have data like this

    00001 000010 A
    00001 000010 B
    00002 000005 M
    00002 000005 A
    00002 000010 M
    00001 000005 B

    I want sort the data with First is with ‘M’ value after that A,B,C,D… like normal order only.
    My sort order should be like this

    00001 000010 M
    00001 000010 A
    00001 000010 B


    00002 000005 M
    00002 000005 A
    00002 000005 B


    Please any body can help me.

    Like

    • Hi Chandra hope u r fine,
      check this ,
      — here is your data

      insert into #t values
      (‘00002 000005 A’),
      (‘00001 000010 A’),
      (‘00002 000005 M’),
      (‘00002 000005 B’),
      (‘00001 000010 B’),
      (‘00001 000010 M’)

      –Normat Output will be : select * from #T order by Id;

      Output
      ———
      00001 000010 A
      00001 000010 B
      00001 000010 M
      00002 000005 A
      00002 000005 B
      00002 000005 M

      — I don’t know, what exactly you want,but here i am adding one solution ,please check this..

      ;With Orders(ID,Order1,Order2)
      as
      (
      select Id,TT,case when Id like ‘%M%’ then 0 else T end as RR from
      (
      select *,row_number() over (partition by SUBSTRING(Id,0,len(Id)-1) order by Id) as T,
      dense_rank() over (order by SUBSTRING(Id,0,len(Id)-1)) as TT from #T
      ) as Inline
      )
      select ID from Orders order by Order1,Order2

      This Output will be like ..
      —————–

      00001 000010 M
      00001 000010 A
      00001 000010 B
      00002 000005 M
      00002 000005 A
      00002 000005 B

      If its fine then ..Cheers :-)

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s