A Little About TOP WITH TIES In SQL Server
Video Summary
In this video, I dive into the concept of `TOP (1) WITH TIES` in SQL Server and address some common confusion around why it might return more rows than expected. Erik Darling from Darling Data explains how the lack of an appropriate tiebreaker can lead to returning all matching rows instead of just one. I walk through a practical example using a hypothetical post table, demonstrating that without an order-by clause or a proper tie-breaking column, `TOP (1) WITH TIES` will return all rows that match the criteria, not just one. The video also covers how to use window functions like `DENSE_RANK()` to better understand and control which rows are returned when using this query construct.
Full Transcript
Erik Darling here with Darling Data and in today’s video I’m going to attempt to answer a question because I posted a video where I described using top one with ties a little bit some time ago and some people still didn’t get it. Did not understand why top one with ties would return a lot of rows sometimes. some ties. So we’re gonna talk a bit about that. I don’t know. Honestly, it’s Saturday here and I’m not feeling terribly creative and I just need something easy to do right now. So screw it. If you like this channel and you would like to support my endeavors to bring you usually very thoughtful, energetic, SQL Server content with the occasional screw it, you can sign up for a membership. There’s a link down below. It says join or become a member or something. I forget what. I don’t watch these things. You crazy listening to my own voice, seeing my own face. Woof! Why would I put myself through that? If you would like to support this channel in some other way, perhaps $4 a month is just too rich for your blood, you can like, you can comment, you can subscribe. And of course you can ask us to ask me questions for free privately that I will answer publicly during my office hours episodes where I answer five user submitted questions at a time. You can ask whatever you want. I don’t care. If you need consulting help with SQL Server, still powerhouse number one. SQL Server consultant outside of New Zealand. Beer Gut Magazine says so, so it must be so. Whether you need health checks, performance analysis, hands on tuning, dealing with SQL Server, dealing with SQL Server, dealing with SQL Server performance emergencies, or teaching your developers to not be such dimwits so you have fewer performance emergencies, all of these things become possible through yours truly at a very reasonable rate. So, get at me with that. Anyway, if you would like to get some performance tuning training content from me, you can get all 24 hours of my currently available stuff at that URL.
Well, with that discount code, it comes down to about 150 USD and that lasts you for the rest of your life. There is no subscription necessary. If you would like to get in on the presale price for my upcoming T-SQL course, you can get it now for 250 bucks. That will not last forever. And if you wait and it goes up to 500 bucks and you’re like, hey, can I get a discount? The answer is no. You missed out. You will have had months to do this. I urge you to do it now rather than later. When it will cost you twice as much. This is, of course, companion material to the pre-cons that Kendra Little and I will be teaching in Seattle this November about T-SQL. So, if you’re going to attend those pre-cons, you get all this stuff for free. Well, not for free. You get it with the price of admission, which if your company is paying for it, that basically makes it for free.
But this is work-related stuff, so I would hope that your company would pay for or at least reimburse you for buying this. All right. It would be kind of crazy to not. Anyway, speaking of leaving the house, I will be on tour with Redgate all summer long. I feel just like Lars Ulrich. I think that’s how you say his name.
New York City, August 18th to 20th. Dallas, September 15th to 17th. Then Amsterdam, October 1st to 3rd. And that all leading up to the main event at the Past Data Community Summit, Seattle, November 17th to 21st. Come hang out. Watch me be a SQL Server monkey. Live and in person.
With that out of the way, though, let’s talk about these top one with ties. And don’t worry, we’re not going to discuss anything lascivious in this video having to do with ties. We’re not going to say any dirty stuff like foreign hand knot.
Because we don’t tie ties like we’re going to our 8th grade dance. We’re adults. We use half Windsor’s because we are grown people with necks. We’re also not in a talking heads video.
So in the post table, for post type ID 3, there are, I think, 167 rows. So if we run this query, we will get back all 167 rows of post type ID 3. All right. 167 right there.
And then the way that top one with ties works, and actually I should show you this first. So the first thing I’m going to do is run this query without the order by. And honestly, this is perhaps something that should happen with any query where top is involved.
But we actually get an error when we do this. The top end with ties clause is not allowed without a corresponding order by clause. Perhaps this should happen for any top that we use.
I don’t know. But the point is that if we order by post type ID, and we’re filtering to post type ID 3, we’re only going to have post type ID.
So there is no tie breaker when we do this. Is there no semicolon there or there or there? I don’t know. I feel very foolish now. But when we do this, we still get back all 167 rows.
If we make that a little bit wider and we scroll on down, without some sort of tie breaker in place, there is no tie to break. This is all post type ID 3 going out through all the results.
So there is nothing to break our tie. We could break the tie very early if we added in a unique column to the order by. So the ID column is the clustered primary key in this table, which means that it is all unique and every row is unique.
And so we don’t get past the first row when we do this. This just says one row. Cool.
We broke the tie early. There were no ties after that. There were no duplicates in ID. So there are no ties after the ID broke the ties very, very early. We could also add in sort of a late tie breaker, right?
So if we run this and we say top, like select the top one with ties, and we order by owner user ID, we don’t get back 167 rows anymore. We get back eventually after waiting some indeterminate amount of time, we get back 164 rows.
So if we come down here, we will see that we only got 164 instead of 167. There are three missing rows here. Now the owner user ID for all of these is negative one, which if you’re keeping track at home, I think that’s the ID for the community bot for Stack Overflow.
So this is all negative one. The only thing that we can infer from this is that something different than negative one eventually happened, and then we broke the tie there, and then we got nothing further back, right?
So we were able to not return three rows that happened after the tie was broken. If you want to see what breaks the tie, you might want to consider using the dense rank windowing function, where you will see what dense rank does is ranks everything together where the partition is the same, and then as soon as the partition resets, then we get a new one, right?
So rather than like row number, which will give us a contiguous number going up, and rank, which will give us like a weird like broken set of numbers when the new, if there are any ties in a new ranking starts, dense rank gives you contiguous numbers, right?
So if we run this, and we say I want the dense rank of all this stuff partitioned by post type ID, ordered by owner user ID, and then we order by owner user ID on the way out, this first column is our dense rank.
So like I said, unlike row number, which would give us like contiguous numbers counting one through whatever until we got to a new partition thing, this just gives us all one, right? And this kind of makes sense for how the tie is broken, because eventually after all these ones, we get down to a new owner user ID, and then we get 234.
Now if we used rank, this would give us non-contiguous results after the tie. I think there’s a start of like three or something. Actually, let’s just find out real quick. All right, we’ll just take the dense out of that, and I’ll show you.
All right, that makes the most sense to do, right? So if we run this now, oh no, it goes 165. So we had 164 rows of one, and then we have 165, 166, and 167.
Boy, was I silly. So learn T-SQL with Eric. So we have these numbers in here.
So if we put the dense rank back, which is what we wanted to do here anyway, then we will get back all the ones, right? And then 234.
And of course the 234, if we scroll across a little bit to owner user ID, we’ll see that’s where we started getting new values back. So this is what top one with ties does. As long as your ordering elements continue to supply the same value over and over again, you will continue to get rows back until you reach the end of the result set.
Or, I mean, presuming in this case that you never return a tiebreaker like we did with just ordering by post type ID, you’ll just keep getting rows back until you reach the end of the result. Only when you add in a column that eventually breaks the tie do you stop returning ties because the tie has officially been broken and you have found all of the ties available.
So anyway, I hope you enjoyed yourselves. I hope you learned something. I mean, I just spaced on the rank function. That’s my bad.
Oh, man. I’m still going to publish this too. That’s where I’m at. So this is how top one with ties sort of works. And this is how you can get lots and lots of results back if you do not have an adequate tiebreaker at some point in your query.
What that adequate tiebreaker is is between you and your database. I can’t tell you what it should be. I can’t tell you what you should use there.
All I can say is use your best judgment. All right. Cool. Thank you for watching. Goodbye.
Bye. Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.