Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0335 - SQL Server - Using the WITH TIES option in the TOP clause of SELECT queries

Apr 28 2014 12:00AM by Nakul Vachhrajani   

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

image

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

image

NOTES:

  • 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,

Be courteous. Drive responsibly.

Tags: #SQLServer, SQL Server, T-SQL, #TSQL, Development, Guidance


Nakul Vachhrajani
4 · 36% · 11648
3
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"#0335 - SQL Server - Using the WITH TIES option in the TOP clause of SELECT queries" rated 5 out of 5 by 3 readers
#0335 - SQL Server - Using the WITH TIES option in the TOP clause of SELECT queries , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]