T-SQL 101: 27 Using TOP WITH TIES to include matching rows in SQL Server T-SQL Queries

In the previous T-SQL 101 post, I showed the TOP operator. One of the challenges that comes up though, is if I say SELECT TOP (10) FROM dbo.Products ORDER BY Size, what if there are 5 items with the first Size, and 7 products with the second Size? Which rows get returned when I just ask for 10?

Perhaps what you want is the TOP (10) but then, when you get to the end of the 10, if the next one has the same value you might want to continue on. T-SQL has a way of doing this. We say WITH TIES as shown in the code below:

When I used WITH TIES, SQl Server continues to return rows as long as they still match the same value that you are using for the order.

One challenge is that it also means that you do not know how many rows are going to be returned. If the entire table had 600ml as a Size, and I asked for the TOP (10) WITH TIES, I’d get the whole table back because they would all match.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-07-22