asked
Top 2 with Ties
Hi All, create table #temp( sal int) insert into #temp values (100),(100),(90),(90),(80) select top 2 with ties * from #temp order by sal desc expected results 100 100 90 90 to my surprise i see the results as 100 100 why is that so? Thanks!
Hakan and KFeasel give you good ways to get your expected results, but BOL has the answer as to why the original query did not give you the expected results. 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. (emphasis added) So, if your last row has ties, the ties will get brought in. Other rows that are not the last will count against the quota normally. So, if you do something like: create table #temp( sal int) insert into #temp values (100),(90),(90), (90),(80) select top 2 with ties * from #temp order by sal desc Then the results are: 100 90 90 90 as it brings in all of the ties with the last row to be returned.
Here is code that will get what you want: create table #temp( sal int) insert into #temp values (100),(100),(90),(90),(80); with RankedValues as ( select sal, DENSE_RANK() over (order by sal desc) as dr from #temp ) select sal from RankedValues where dr <= 2 /* expected results 100 100 90 90 */ drop table #temp; Check out the Ranking Functions [BOL Link](
http://msdn.microsoft.com/en-us/library/ms189798.aspx) for more details. The basic idea is that we want to use the dense rank to get all records matching one of the top two distinct values. DENSE_RANK counts "matches" as ties, so with two records having values of 100, they will both have a DENSE_RANK result of 1 in this case. Then, the next-highest number (90) will have a DENSE_RANK result of 2, no matter how many of them you have.
Sorry for not giving you an explanation, but it's hard to write long answers and copy reference text etc on an iPhone.
Sorry, if i have posted the question wrongly. I was trying get some insight on WITH TIES option while selecting from a table. I read some where that if WITH TIES used then it would consider all the recods with same same value as one unit.
http://blog.sqlauthority.com/2009/12/23/sql-server-order-by-clause-and-top-with-ties/ but when i tried the above example it did not work. Why is that so?
You do not mention SQL server version, but I assume you are using sql 2005 or later. Try dense_rank() instead ;with cte As ( select dense_rank() over(order by colX) as rankZ from tableY ) Select * from cte where rankZ <3
