When working with the AdventureWorks database, assume that we have the following requirement:
Please provide the list of customers with TOP 100 orders from Australia with the highest order totals.
The following query is the first one that comes to mind:
USE AdventureWorks2012; GO SELECT TOP 100 soh.CustomerID, soh.TotalDue, soh.TerritoryID FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesTerritory AS sst ON soh.TerritoryID = sst.TerritoryID WHERE sst.CountryRegionCode = 'AU' ORDER BY soh.TotalDue DESC; GO
This query would work in most cases, but analyzing the requirement, we realize that we don’t want the TOP 100 orders, we want all customers with the TOP 100 orders. These two requirements are different, and here’s why:
- TOP 100 orders by total value = this means that we should have only 100 orders being returned, in the order defined by the ORDER BY clause
- Customers with TOP 100 orders by total value = this means that we need to first fetch the TOP 100 order totals and then look for all customers which have orders with these totals. In this case, the number of orders being returned may be more than 100
In this case, we need to use the WITH TIES clause when defining the TOP statement. Here’s the query:
USE AdventureWorks2012; GO SELECT TOP 100 WITH TIES soh.CustomerID, soh.TotalDue, soh.TerritoryID FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesTerritory AS sst ON soh.TerritoryID = sst.TerritoryID WHERE sst.CountryRegionCode = 'AU' ORDER BY soh.TotalDue DESC; GO
- The TOP…WITH TIES can only be used in a SELECT statement
- It cannot be used without an accompanying ORDER BY clause (a normal TOP can)
- The TOP…WITH TIES also works with the TOP (n) PERCENT option
Until we meet next time,
Nice Article !
This is my pleasure to read your article.
Really this will help to people of SQL Server Community.
I have also prepared one article about, Explained TOP Clause with TIES Option of SQL Server.
You can also visit my article, your comments and reviews are most welcome.