SQL Server

SQL Server and Access support the TOP clause. To get the top five most expensive products, you need this command:

SELECT TOP 5 Name, Price
FROM Product
ORDER BY Price DESC, Name ASC;

With SQL Server, you can alternatively use SET ROWCOUNT to limit the number of returned rows. Here's an example:

SET ROWCOUNT 5;
SELECT Name, Price
FROM Product
ORDER BY Price DESC, Name ASC;

Also, you can limit by specifying what percent of the result set to return. The following command returns the top 15 percent of most expensive products:

SELECT TOP 15 PERCENT Name, Price
FROM Product
ORDER BY Price DESC, Name ASC;

MySQL

MySQL has the LIMIT keyword that has similar functionality. The following is the MySQL command that returns the first five most expensive products:

SELECT Name, Price
FROM Product
ORDER BY Price DESC, Name ASC
LIMIT 0,5;

As you can see, the LIMIT clause has two parameters. The first one specifies the index of the first returned record (starting with zero), and the second specifies how many rows to return.

Oracle

With Oracle, you use the ROWNUM pseudocolumn to limit the number of returned rows:

SELECT Name, Price FROM
      (SELECT Name, Price
       FROM Product
       ORDER BY Price DESC, Name ASC)
WHERE ROWNUM<=5;

This technique works only when you restrict the query for a ROWNUM that starts with one (such as ROWNUM <=5). If you try to restrict using another range, such as ROWNUM BETWEEN m and n, where m is greater than one, the query will return no results. You'll see a workaround to this limitation in the next exercise.

ROWNUM is a pseudocolumn that associates a row number to each record returned by a query. What's important to note here is that ROWNUM values are assigned before the ORDER BY clause executes. For this reason, you need to use a subselect instead of simply limiting by ROWNUM in the first place, like this:

SELECT Name, Price
FROM Product
WHERE ROWNUM<=5
ORDER BY Price DESC, Name ASC

This query doesn't return the expected results because the WHERE filter is applied first and takes the first five rows (in a random order). These rows are then ordered by Price and Name (which isn't what you intended to do).

DB2

Using DB2, you can list the top five most expensive products using FETCH FIRST n ROWS ONLY:

SELECT Name, Price
FROM Product
ORDER BY Price DESC, Name ASC
FETCH FIRST 5 ROWS ONLY;

MySQL

With MySQL, this problem turns out to be a piece of cake. With MySQL, you use the LIMIT keyword, just like in the previous example but using different parameters this time:

SELECT ProductID, Name
FROM Product
ORDER BY ProductID
LIMIT 5,5;

This time you specified 5 as the first parameter (instead of 0), mentioning that you want retrieve rows starting with the sixth record in the result set (remember that the row number is zero-based). The second parameter specifies how many rows you want to retrieve.

Oracle

With Oracle, you use a command similar to the one you saw in the previous example. However, there's a catch—you can't limit the result set based on the ROWNUM pseudocolumn being between m and n if m>1. If you execute the following query, no rows will be returned:

SELECT ProductID, Name FROM
     (SELECT ProductID, Name
      FROM Product
      ORDER BY ProductID)
WHERE ROWNUM BETWEEN 6 AND 10;

The workaround to this problem is as follows:

SELECT ProductID, Name FROM
     (SELECT ProductID, Name, ROWNUM AS rn FROM
          (SELECT ProductID, Name
           FROM Product
           ORDER BY ProductID)
     ) inner
WHERE inner.rn BETWEEN 6 AND 10;

Here you "saved" the ROWNUM values in a new row of the subquery, so you could filter correctly the results in the outer query.

Alternatively, and more elegantly, you can use the RANK analytic function, as described in Chapter 4, "Summarizing and Grouping Data":

SELECT ProductID, Name FROM
(
SELECT RANK() OVER (ORDER BY ProductID) As Ranking, ProductID, Name
FROM Product
ORDER BY PRODUCTID
)
WHERE Ranking BETWEEN 6 AND 8;

SQL Server

SQL Server doesn't provide you with a simple way to achieve your goal. The TOP clause works well as long as you're only interested in the top records of a query. There are a number of workarounds for this limitation.

Let's discuss the most obvious solution first. This uses the SQL-99 syntax (so with some tweaking it works with other databases as well) and uses correlated subqueries (correlated subqueries were discussed in Chapter 6, "Combining SQL Queries"):

SELECT ProductID, Name
FROM Product AS P1
WHERE
  (SELECT COUNT(*) FROM Product AS P2
   WHERE P1.ProductID >= P2.ProductID) BETWEEN 6 AND 10
ORDER BY ProductID;

Remember that a correlated subquery executes once for every record of the outer query. In this example, for each product returned by the outer query, the subquery calculates how many products have a lower ID. You're looking for products for which that number is between six and 10.

The second method uses the TOP function. The idea is simple, but the SQL code will look a bit awkward at first. If you want to retrieve products between six and 10, here's a working strategy:

• First, you get the TOP 10 products, ordered by ProductID (ascending). The products you're interested in are the bottom of this list.

• You order the resulting list in descending order by ProductID (so the rows you're interested in will be at the TOP).

• You get the TOP 5 products from list.

• You finally sort the remaining products in ascending order by ProductID.

By using subqueries, you can implement all these steps in a single SQL command:

SELECT ProductID, Name
FROM
  (SELECT TOP 5 ProductID, Name
   FROM
     (SELECT TOP 10 ProductID, Name
      FROM Product
      ORDER BY ProductID) AS P1
   ORDER BY ProductID DESC) AS P2
ORDER BY ProductID

The last technique I'll present for SQL Server consists of using a temporary table with an IDENTITY column. This solution is versatile so that it can also be implemented for other databases as well. For more information about temporary tables and IDENTITY columns, please read Chapter 12. The code for this solution, presented below, is self-explanatory:

/* Create a temporary table named #Product, having an IDENTITY column */
CREATE TABLE #Product
(RowNumber SMALLINT NOT NULL IDENTITY(1,1),
 ProductID INT,
 Name VARCHAR(50))
/* Populate the temporary table with records from the table we browse through.
   The RowNumber column will generate consecutive numbers for each product.
   We're free to order the list of products by any criteria. */
INSERT INTO #Product (ProductID, Name)
SELECT ProductID, Name
FROM Product
ORDER BY Price DESC

/* Retrieve the requested group or records from the temporary table */
SELECT ProductID, Name
FROM #Product
WHERE RowNumber BETWEEN 6 and 10

/* Drop the temporary table */
DROP TABLE #Product

DB2

The first query that was presented for SQL Server (using correlated subqueries) works with no modifications with DB2. Here it is again, for reference:

SELECT ProductID, Name
FROM Product AS P1
WHERE
  (SELECT COUNT(*) FROM Product AS P2
   WHERE P1.ProductID >= P2.ProductID) BETWEEN 6 AND 10
ORDER BY ProductID;

The second solution is DB2 specific. It uses the ROWNUMBER function in a sub-query to generate a "rownumber" fabricated column, which is then used to filter and retrieve the specified range of products:

WITH temp AS
  (SELECT ProductID, Name,
           ROWNUMBER() OVER(ORDER BY ProductID) AS row
   FROM Product)
SELECT ProductID, Name FROM temp WHERE row>5 and row<=10

ROWNUMBER, RANK, and DENSERANK are three analytical functions supported by DB2 that come in handy when you need to perform tasks that imply record numbering or ranking. The functions RANK and DENSE_RANK are also supported by Oracle, along with several others. (Chapter 4, "Summarizing and Grouping Data," demonstrates how to use the RANK function.) You'll also see them in action again, later in this case study. For detailed information about the analytic functions supported and how they work, please refer to your vendor's SQL reference manual.

Getting the Same Results using Table Joins

The solution presented earlier is probably the most commonly used. However, as anticipated, you can obtain the same results using joins, with a much more elegant query. This solution also works with MySQL, which doesn't support subqueries:

SELECT DISTINCT P1.ProductID, P1.Name
FROM Product P1
  INNER JOIN ProductCategory PC1
    ON P1.ProductID = PC1.ProductID
  INNER JOIN ProductCategory PC2
    ON PC1.CategoryID = PC2.CategoryID
WHERE PC2.ProductID = 6
ORDER BY Name;

Notice that, in this example, we've used some table aliases to make the SQL code a bit shorter (ProductCategory is, after all, quite a long word to type!).

This is the same query, written with the alternate syntax (which works on Access):

SELECT DISTINCT P1.ProductID, P1.Name
FROM Product P1, ProductCategory PC1, ProductCategory PC2
WHERE P1.ProductID = PC1.ProductID
  AND PC1.CategoryID = PC2.CategoryID
  AND PC2.ProductID = 6
ORDER BY Name;

Wow, you have just joined three tables! And, to add to the confusion, you used two instances of the ProductCategory table. The action of joining two instances of the same table is called a self-join, and it works just as if they were two separate tables.

Joining the two ProductCategory instances on the CategoryID field generates a list of ProductID/ProductID pairs, containing products associated with the same category.

Now that you have this list of products, which are related by their category, the rest is simple: In the right side of the list, you filtered according to the ProductID that you were looking for (6 in this case). In this manner, in the left side of the list there remained only the products related to the product with an ID of 6. Finally, in the left side of the list you joined with the Product table in order to get the names and descriptions of the resulted products.

Note that you still need the DISTINCT keyword to filter duplicate records from products that belong to more than one of the resulting categories.

Getting the Same Results using Correlated Subqueries

This method is less obvious, but it's good to know that there are multiple ways to get the same results with SQL. In this particular scenario, the solution using table joins is likely to be faster, but in practice remember that only a good set of tests can show you what's the best solution for your database:

SELECT C.Name as "Category Name", Product.Name as "Product Name"
FROM Product, Category C
WHERE Product.ProductID IN
  (SELECT ProductID FROM ProductCategory
   WHERE ProductCategory.CategoryID = C.CategoryID)
ORDER BY C.Name, Product.Name;

This query is a bit more complicated than the previous ones—if you have problems understanding it, please reread the section on correlated subqueries in Chapter 6, "Combining SQL Queries."

What you did was to ask from the start for a category name and product name without specifying anything about the linking table, ProductCategory. In the outer query, you asked only for the columns you were interested in without specifying how they're related. However, for each category, the WHERE clause uses the correlated subquery to filter out only the products that are associated with it.

Remember that correlated subqueries execute for each record of the main query. In this case, for each category returned by the outer query, the correlated subquery executes and returns the products associated with that category. The outer query gets the results back and uses them to compose the list containing category and product names.

The important detail to understand is the way the categories from the outer query are used in the subquery:

WHERE ProductCategory.CategoryID = C.CategoryID)

This line does the whole trick: The category of the outer query (C.CategoryID) is used in a table join in the correlated subquery. The category ID from the outer query is referenced through the C alias defined for Category.

SQL Server

For SQL Server, here's the query that does the trick. You get the top two products for each category using a correlated subquery:

SELECT C.Name AS "Category", Product.Name AS "Product",
        Product.Price as "Price"
FROM Product, Category C
WHERE Product.ProductID IN
  (SELECT TOP 2 ProductID FROM ProductCategory
   WHERE ProductCategory.CategoryID = C.CategoryID
   ORDER BY Price DESC)
ORDER BY C.Name, Product.Price DESC;

DB2

DB2 doesn't allow you to use FETCH FIRST n ROWS ONLY in subqueries, so you can't simply translate the SQL Server version (which uses TOP) to a DB2 equivalent.

The DB2 solution uses the ROWNUMBER function, which you already saw in an earlier example. However, this time you use PARTITION BY in combination with ORDER BY to create a ranking for each product in descending order of its price, for each category in part:

WITH tmp (CategoryName, ProductName, ProductPrice, rank)
AS
    (SELECT C.Name, P.Name, P.Price,
            ROWNUMBER() OVER (PARTITION BY C.Name ORDER BY P.Price DESC)
            AS rank
    FROM Product P
    JOIN ProductCategory PC ON P.ProductID = PC.ProductID
    JOIN Category C ON C.CategoryID = PC.CategoryID)

SELECT CategoryName, ProductName, ProductPrice
FROM tmp
WHERE rank <= 2
ORDER BY CategoryName

PARTITION BY C.Name specifies that you want the products ranking created separately for each category. ORDER BY P.Price DESC specifies that, for each category, you want the products sorted in descending order of their price.

ROWNUMBER (or ROW_NUMBER) always returns a unique number for each returned row. DB2 also supports two similar functions, RANK and DENSERANK (or DENSE_RANK). RANK and DENSERANK return the same value for records that are equal in respect to the ORDER BY criterion—in this case, if two or more products of a category have the same price, they'll receive the same ranking. The difference between RANK and DENSERANK is that RANK leaves gaps in rankings when two or more products have the same ranking. Say, for example, if the first three most expensive products have the same price, they'll all receive a ranking of 1. The following product in the list will have a RANK of 4 but a DENSERANK of 2 because DENSERANK leaves no gaps.

Oracle

With Oracle, you apply a similar method as for DB2, except that the syntax is a little different. Oracle supports RANK and DENSE_RANK just like DB2, but it doesn't support ROW_NUMBER. For this reason the Oracle query will not be guaranteed to retrieve a maximum of two products for each category:

SELECT CategoryName, ProductName, ProductPrice
  FROM
    (SELECT C.Name as CategoryName,
             P.Name as ProductName,
             P.Price as ProductPrice,
             RANK() OVER (PARTITION BY C.Name ORDER BY P.Price DESC)
             AS rank
     FROM Product P
     JOIN ProductCategory PC ON P.ProductID = PC.ProductID
     JOIN Category C ON C.CategoryID = PC.CategoryID)
WHERE rank <= 2
ORDER BY CategoryName;

Getting the Same Results using Table Joins

Using table joins, you need to join the Product, ProductCategory, and Category tables. You don't need to also join the Department table because you don't need anything from it (its name or description). You filter the results based on the DepartmentID field of the Category table:

SELECT Product.ProductID, Product.Name
FROM Product
   INNER JOIN ProductCategory
   ON Product.ProductID = ProductCategory.ProductID
   INNER JOIN Category
   ON ProductCategory.CategoryID = Category.CategoryID
WHERE Category.DepartmentID = 1
ORDER BY Product.Name;

The same query, without using JOIN, looks like this (note that this form will work on Access and pre-9i versions of Oracle):

SELECT Product.ProductID, Product.Name
FROM Product, ProductCategory, Category
WHERE Product.ProductID = ProductCategory.ProductID
   AND ProductCategory.CategoryID = Category.CategoryID
   AND Category.DepartmentID = 1
ORDER BY Product.Name;

Getting the Same List with a Correlated Subquery

If you prefer using correlated subqueries, this is the way to go:

SELECT D.Name AS "Department", P.Name AS "Product"
FROM Product P, Department D
WHERE ProductID IN
     (SELECT ProductID
      FROM ProductCategory INNER JOIN Category
      ON ProductCategory.CategoryID = Category.CategoryID
      WHERE Category.DepartmentID = D.DepartmentID)
ORDER BY D.Name, P.Name;

On the outer query, you only specify the fields in which you're interested and enforce the relationships between them using the subquery. The correlated (inner) subquery is called for each department returned by the outer query, and it returns the ProductIDs associated with that department.

Note that you have flexibility to write the subquery in any number of ways, including using another subquery instead of table joins, like this:

SELECT D.Name AS "Department", Product.Name AS "Product"
FROM Product, Department D
WHERE ProductID IN
     (SELECT ProductID from ProductCategory
      WHERE ProductCategory.CategoryID IN
             (SELECT CategoryID FROM Category
              WHERE D.DepartmentID = DepartmentID))
ORDER BY D.Name, Product.Name;

You'll rarely want to complicate your queries like this, but it demonstrates how flexible SQL can be.

Getting the Same List with Correlated Subqueries

You can obtain the same results using correlated subqueries. There are two main ways you can rewrite the previous queries using correlated subqueries.

In the first example, you first group the products by department using GROUP BY and perform a COUNT for each group:

SELECT D.Name AS "Department", COUNT(P.Name) AS "Products"
FROM Department D, Product P
WHERE ProductID IN
     (SELECT ProductID
      FROM ProductCategory INNER JOIN Category
      ON ProductCategory.CategoryID = Category.CategoryID
      WHERE Category.DepartmentID = D.DepartmentID)
GROUP BY D.Name
ORDER BY D.Name;

In the second solution, you use a subquery to calculate the number of products for each department instead of using GROUP BY to group by departments. Note that the subquery is still a correlated subquery, and it executes once for each department selected by the outer query:

SELECT D.Name AS "Department",
     (SELECT COUNT(ProductID)
      FROM ProductCategory INNER JOIN Category
      ON ProductCategory.CategoryID = Category.CategoryID
      WHERE Category.DepartmentID = D.DepartmentID) AS "Products"
FROM Department D;