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.

Solarwinds

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

SQL SERVER - ORDER BY Clause and TOP WITH TIES Orderwithties

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 (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQLAuthority News – Meeting SQL Expert Imran at Hyderabad
Next Post
SQL SERVER – CDC and TRUNCATE – Cannot truncate table because it is published for replication or enabled for Change Data Capture

Related Posts

24 Comments. Leave new

  • Hi Pinal,

    There is no difference in Execution plan

    With Regards
    Vijayakumar.

    Reply
  • So wether you use WITH TIES depends on what you expect to get out of your data.

    Reply
  • Hi Pinal,
    Never used with ties clause, thanks for bringing out the feature/article and explanation.

    Reply
  • In what real time scenario WITH TIES is used?

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

      Reply
  • hi,
    while transaction can we need to allocate any memory?

    thanks and regards
    ravindra

    Reply
  • Wayne Erfling
    July 13, 2010 7:05 pm

    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.

    Reply
  • Thanks for explaining with ties clause.I was struggling with it.

    Reply
  • how to retrieve top 100 records to top 1000 records from single table

    Reply
  • Hi pinal dave,

    How to retrieve top 10 records which are recently updated?
    and

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

    Reply
    • Shekhar Teke, Sr DBA, Talkingtech Limited, NZ
      January 14, 2013 3:12 am

      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)

      Reply
      • nice.. query with orderby…
        it gives same result set by using isnull operator too..
        Thank you

      • Bhaskar Shetty
        June 4, 2013 2:31 pm

        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.

  • Thanks…!!

    Reply
  • Nice reply

    Reply
  • 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,

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

    Reply
    • Shekhar Teke, Sr DBA, Talkingtech Limited, NZ
      January 14, 2013 3:53 am

      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

      Reply
  • Chandra Mohan G
    January 21, 2014 11:24 pm

    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.

    Reply
    • 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 :-)

      Reply
  • Chandra Mohan G
    January 21, 2014 11:30 pm

    Data is single column only and i should not use any group here

    Reply
  • please explain me outer apply and left outer join difference..?

    Reply

Leave a Reply

Menu