Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 97

5

Because the request involves activity in the year 2007, the query should have a WHERE clause with 

the appropriate date range filter (orderdate >= ‘20070101’ AND orderdate < ‘20080101’). Because the 

request involves average freight values per shipping country and the table can have multiple rows per 

country, the query should group the rows by country, and calculate the average freight. To get the 

three countries with the highest average freights, the query should specify TOP (3), based on logical 

order of average freight descending. Here’s the complete solution query.

SELECT TOP (3) shipcountry, AVG(freight) AS avgfreight 

FROM Sales.Orders 

WHERE orderdate >= '20070101' AND orderdate < '20080101' 

GROUP BY shipcountry 

ORDER BY avgfreight DESC;

Remember that in SQL Server 2012 you can use the standard OFFSET-FETCH option instead of the 

proprietary TOP option. Here’s the revised solution using OFFSET-FETCH.

SELECT shipcountry, AVG(freight) AS avgfreight 

FROM Sales.Orders 

WHERE orderdate >= '20070101' AND orderdate < '20080101' 

GROUP BY shipcountry 

ORDER BY avgfreight DESC 

OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;

6

Because the exercise requests that the row number calculation be done for each customer separately, 

the expression should have PARTITION BY custid. In addition, the request was to use logical ordering 

by orderdate, with orderid as a tiebreaker. Therefore, the OVER clause should have ORDER BY order-

date, orderid. Here’s the complete solution query.

SELECT custid, orderdate, orderid, 

  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum 

FROM Sales.Orders 

ORDER BY custid, rownum;

www.it-ebooks.info

---