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 (https://blog.sqlauthority.com)
24 Comments. Leave new
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.
Thanks for explaining with ties clause.I was struggling with it.
how to retrieve top 100 records to top 1000 records from single table
Search for Pagination in this site
Hi pinal dave,
How to retrieve top 10 records which are recently updated?
and
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.
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)
nice.. query with orderby…
it gives same result set by using isnull operator too..
Thank you
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…!!
Nice 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,
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
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
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.
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 :-)
Data is single column only and i should not use any group here
please explain me outer apply and left outer join difference..?