How Does TOP WITH TIES Work in SQL Server? – Interview Question of the Week #159

Question: How Does TOP WITH TIES Work in SQL Server?

Answer: This is a very old question I just encountered in the recent Comprehensive Database Performance Health Check.

How Does TOP WITH TIES Work in SQL Server? - Interview Question of the Week #159 topwithties-800x326

Let us first understand how TOP WITH TIES works in simple words.

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.

Let us understand with a simple example:

Run following example where we will see each count of the items with OrderQty.

-- Total Count of Rows Grouped by ORDERQty
SELECT COUNT(*) Cnt, OrderQty
FROM Sales.SalesOrderDetail
GROUP BY OrderQty
ORDER BY OrderQty
GO

How Does TOP WITH TIES Work in SQL Server? - Interview Question of the Week #159 topwithties1

Here you will see that we have 74954 items with OrderQty 1 and 7394 items with OrderQty 4.

Next, we will run the following query with the keyword TOP 10, it will bring us following resultset with 10 rows.

-- Example of Top 10 Records
SELECT TOP 10 *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 1
ORDER BY OrderQty
GO

How Does TOP WITH TIES Work in SQL Server? - Interview Question of the Week #159 topwithties2

Next, we will run the same command and we will have additional keyword TOP 10 with Ties.

-- Example of Top 10 WITH TIES
SELECT TOP 10 WITH TIES *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 1
ORDER BY OrderQty
GO

How Does TOP WITH TIES Work in SQL Server? - Interview Question of the Week #159 topwithties3

This time instead of just returning only 10 rows, it will return all the rows where OrderQty = 1. This is because when we use TOP WITH TIES it returns all the rows where the OrderQty is 10.

I hope from this example it is clear how TOP works and how it’s behavior changes when we use TOP WITH TIES.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Order By, SQL Scripts, SQL Server
Previous Post
How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158
Next Post
How to Schedule a Job in SQL Server? – Interview Question of the Week #160

Related Posts

2 Comments. Leave new

  • Rama Koteswararao
    February 5, 2018 9:53 am

    In this case, What is the difference between Where clause and Top with TIES option ?

    Reply
    • His example wasn’t very good, because he “where’d” the same column he used to sort.

      TOP WITH TIES is meant to bring all records with same value as the value of the last record that would come.

      Since in his example all records that passed the WHERE chause has the same value, WHERE WITH TIES had no purpose. But if he hadn’t used the WHERE clause, the query would bring all records with value of 1 (considering that’s the lowest value) and no records with value higher than that.

      Reply

Leave a Reply