Congratulations to Yuriy Maksimov, a DBA for Ultralink, Inc. in Costa Mesa, California, and Diosdado F. Veneracion, a data warehouse developer for Fujitsu Consulting in Edmonton, Alberta, Canada. Yuriy won first prize of \$100 for the best solution to the March Reader Challenge, "Recent Orders." Diosdado won second prize of \$50. Here’s a recap of the problem and the solution to the March Reader Challenge.

### Problem:

Jack is a database programmer at a company that sells products online. He writes SQL queries and stored procedures to generate reports based on user orders. He needs to provide a report that lists the two most recent orders placed by each user. The details of each order are stored in a table that the following code creates:

CREATE TABLE order_details (
userid         int NOT NULL,
ordernum   int NOT NULL,
orderdate   datetime NOT NULL,
shipdate     datetime NOT NULL,
PRIMARY KEY ( userid, ordernum ),
UNIQUE ( userid, orderdate )
)
-- Sample data:
INSERT INTO order_details VALUES( 1, 1, '7/1/01', '7/5/01' )
INSERT INTO order_details VALUES( 1, 2, '7/10/01', '7/7/01' )
INSERT INTO order_details VALUES( 1, 3, '7/5/01', '7/2/01' )
INSERT INTO order_details VALUES( 2, 4, '8/1/01', '9/5/01' )
INSERT INTO order_details VALUES( 3, 5, '10/10/01', '10/7/01' )
INSERT INTO order_details VALUES( 3, 6, '10/1/01', '10/2/01' )
INSERT INTO order_details VALUES( 3, 7, '10/24/01', '10/30/01' )
INSERT INTO order_details VALUES( 3, 8, '9/24/01', '9/30/01' )

Help Jack write a query that will return a row that contains the order number, ship date, and order date of the two most recent orders for each user.

### Solution:

Jack can construct the required query in two parts. He can use the following query to retrieve the latest order date for each user:

SELECT o.userid, max( o.orderdate )
FROM order_details AS o
GROUP BY o.userid

Jack can use this query in a derived table called lo, as lines 6 through 10 below, show and join that table to the order_details table to get the details for the latest order:

/*  1 */ SELECT o1.userid,
/*  2 */        o1.ordernum AS lastorder,
/*  3 */        o1.shipdate AS lastshipdate,
/*  4 */        o1.orderdate AS lastorderdate
/*  5 */   FROM order_details AS o1
/*  6 */   JOIN (
/*  7 */         SELECT o.userid, MAX( o.orderdate )
/*  8 */           FROM order_details AS o
/*  9 */          GROUP BY o.userid
/* 10 */      ) AS lo( userid, lastorderdate )
/* 11 */     ON lo.userid = o1.userid
/* 12 */    AND lo.lastorderdate = o1.orderdate

Similarly, Jack can obtain the two most recent orders for each user by using a TOP subquery in the WHERE clause:

/*  1 */ SELECT o1.userid,
/*  2 */        o1.ordernum,
/*  3 */        o1.shipdate,
/*  4 */        o1.orderdate
/*  5 */   FROM order_details AS o1
/*  6 */  WHERE o1.orderdate IN ( SELECT TOP 2 o2.orderdate
/*  7 */                            FROM order_details AS o2
/*  8 */                           WHERE o1.userid = o2.userid
/*  9 */                           ORDER BY o2.orderdate DESC )

Grouping on the userid column and using the lastorderdate value for each userid, as calculated in the derived table lo in the SELECT list, pivots the results as needed for this problem. The complete query, uses the expression in line 2 to obtain the latest order number by aggregating over the rows that have the same orderdate as the lastorderdate. The expression in line 5 obtains the next-to-last order number by aggregating over the rows whose orderdate is less than the lastorderdate.

/*  1 */ SELECT o1.userid,
/*  2 */        MAX(CASE WHEN o1.orderdate = lo.lastorderdate THEN o1.ordernum
END) AS lastorder,
/*  3 */        MAX(CASE WHEN o1.orderdate = lo.lastorderdate THEN o1.shipdate
END) AS lastshipdate,
/*  4 */        MAX(CASE WHEN o1.orderdate = lo.lastorderdate THEN o1.orderdate
END) AS lastorderdate,
/*  5 */        MIN(CASE WHEN o1.orderdate

Alternatively, Jack can obtain the same results by performing a series of self joins with order_details to get the next-to-last order each user placed. The following query obtains the two most recent orders for each user:

/*  1 */ SELECT o1.userid,
/*  2 */           o1.ordernum AS lastorder,
/*  3 */           o1.shipdate AS lastshipdate,
/*  4 */           o1.orderdate AS lastorderdate,
/*  5 */           o2.ordernum AS nexttolastorder,
/*  6 */           o2.shipdate AS nexttolastshipdate,
/*  7 */           o2.orderdate AS nexttolastorderdate
/*  8 */   FROM order_details AS o1
/*  9 */     JOIN (
/* 10 */         SELECT o.userid, MAX( o.orderdate )
/* 11 */           FROM order_details AS o
/* 12 */         GROUP BY o.userid
/* 13 */       ) AS lo( userid, lastorderdate )
/* 14 */     ON lo.userid = o1.userid
/* 15 */   AND lo.lastorderdate = o1.orderdate
/* 16 */    LEFT JOIN order_details AS o2
/* 17 */      ON o1.userid = o2.userid
/* 18 */    AND o2.orderdate = ( SELECT MAX( o3.orderdate )
/* 19 */                                       FROM order_details AS o3
/* 20 */                                       WHERE o3.userid = o1.userid
/* 21 */                                           AND o3.orderdate

The left join ensures that the query also returns users who have just one order. The correlated subquery uses the order_details table, aliased as o3, to get the next-to-last order date for each user. To retrieve the details, the code joins the newest date from this subquery to the order_details table.