Sql server® 2012 t-sql fundamentals

 

CHAPTER 6

 

Set Operators

 

 203

The fact that you cannot specify ORDER BY with the individual queries involved in the set operator 

might also cause logical problems. What if you need to restrict the number of rows in those queries 

with the TOP or OFFSET-FETCH option? Again, you can resolve this problem with table expressions. 

Recall that an ORDER BY clause is allowed in a query with TOP or OFFSET-FETCH, even when the 

query is used to define a table expression. In such a case, the ORDER BY clause serves only as part of 

the filtering specification and has no presentation meaning.

So if you need a query with TOP or OFFSET-FETCH to participate in a set operator, simply define a 

table expression and have an outer query against the table expression participate in the operator. For 

example, the following code uses TOP queries to return the two most recent orders for those employ-

ees with an employee ID of 3 or 5.

SELECT empid, orderid, orderdate 

FROM (SELECT TOP (2) empid, orderid, orderdate 

      FROM Sales.Orders 

      WHERE empid = 3 

      ORDER BY orderdate DESC, orderid DESC) AS D1 

 

UNION ALL 

 

SELECT empid, orderid, orderdate 

FROM (SELECT TOP (2) empid, orderid, orderdate 

      FROM Sales.Orders 

      WHERE empid = 5 

      ORDER BY orderdate DESC, orderid DESC) AS D2;

This query returns the following output.

empid       orderid     orderdate 

----------- ----------- ----------------------- 

3           11063       2008-04-30 00:00:00.000 

3           11057       2008-04-29 00:00:00.000 

5           11043       2008-04-22 00:00:00.000 

5           10954       2008-03-17 00:00:00.000

Here’s the logical equivalent using OFFSET-FETCH.

SELECT empid, orderid, orderdate 

FROM (SELECT empid, orderid, orderdate 

      FROM Sales.Orders 

      WHERE empid = 3 

      ORDER BY orderdate DESC, orderid DESC 

      OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS D1 

 

UNION ALL 

 

SELECT empid, orderid, orderdate 

FROM (SELECT empid, orderid, orderdate 

      FROM Sales.Orders 

      WHERE empid = 5 

      ORDER BY orderdate DESC, orderid DESC 

      OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS D2;

www.it-ebooks.info

---