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.orderdateAlternatively, 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.orderdateThe 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.
APRIL READER CHALLENGE:
Now, test your SQL Server savvy in the April Reader Challenge, "Data Export" (below). Submit your solution in an email message to [email protected] by March 20. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.Stella is a database programmer for a company specializing in hosting publishing information. The publisher database contains tables that store information about authors, publishers, and titles. The company displays a table containing information about publishers on its Web site. Assume that the table schema is the same as the schema for pub_info table in Pubs database. The table includes pr_info column, a text column containing detailed information about each publisher. Stella is trying to export data from this table for a proprietary application that can’t handle text data. She needs to export the data so that the value from pr_info is split across multiple lines, each having no more than 8000 characters. The following example shows how the exported data should be formatted:
Column #1: pub_id Column #2: line_num (from 1 through n) Column #3: line_text Order of rows: pub_id, line_numHelp Stella write the code that will export the publisher information in the desired format efficiently and with minimal coding. She wants a set-based solution to her problem, not a user-defined function (UDF).