Sql server® 2012 t-sql fundamentals

PARTITION BY clause of the function, and use (SELECT ) in the ORDER BY clause of the 

function to indicate that order doesn’t matter. 

Tip

 Using ORDER BY (SELECT ) as the ordering specification for a window func-

tion is one of several ways to tell SQL Server that order doesn’t matter. SQL Server is smart 

enough to realize that the same constant will be assigned to all rows, and therefore it’s not 

necessary to actually sort the data and incur the associated overhead.

Then apply the INTERSECT operator between the two queries with the ROW_NUMBER function. 

Because the occurrences of each row are numbered, the intersection is based on the row numbers in 

addition to the original attributes. For example, in the Employees table, which has four occurrences of 

the location (UK, NULL, London), those occurrences would be numbered 1 through 4. In the Customers 

table, which has six occurrences of the location (UK, NULL, London), those occurrences would be num-

bered 1 through 6. Occurrences 1 through 4 would all be intersected between the two.

Here’s the complete solution code.

SELECT 

  ROW_NUMBER()  

    OVER(PARTITION BY country, region, city 

         ORDER     BY (SELECT 0)) AS rownum, 

  country, region, city 

FROM HR.Employees 

 

INTERSECT 

 

SELECT 

  ROW_NUMBER()  

    OVER(PARTITION BY country, region, city 

         ORDER     BY (SELECT 0)), 

  country, region, city 

FROM Sales.Customers;

www.it-ebooks.info

---