Posted by Erland Sommarskog on 10/23/07 21:40

Chris H (chollstein@broadreachpartnersinc.com) writes:
> I'm pulling multiple campaigns associated with companies so that I
> have a result set with company name, campaign1, campaign2, campaign3,
> campaign4, campaign5 where the campaigns are listed in alpha order (by
> campaign_code) and each campaign column includes only the one campaign
> (or null). When I got to the 5th level of MIN's, the query seemed to
> expand way beyond what was required for the 4th level join and I
> believe there's "bloat" in the expression. The query works as is, but
> I was wondering if anyone has a suggestion for optimizing? I tried to
> retain the tabulation when I pasted from TOAD, but had to do some
> cleanup anyways so sorry if I didn't get it exactly.

Since I don't have your tables, I cannot write a query for them. But here
is an example for the Orders table in the Northwind database that lists
the first five orders for each customer, and in order.

WITH numbered (CustomerID, OrderID, rn) AS (
SELECT CustomerID, OrderID,
row_number() OVER(PARTITION BY CustomerID ORDER BY OrderID)
FROM Orders
)
SELECT CustomerID,
MIN(CASE rn WHEN 1 THEN OrderID END),
MIN(CASE rn WHEN 2 THEN OrderID END),
MIN(CASE rn WHEN 3 THEN OrderID END),
MIN(CASE rn WHEN 4 THEN OrderID END),
MIN(CASE rn WHEN 5 THEN OrderID END)
FROM numbered
GROUP BY CustomerID
ORDER BY CustomerID

This solution requires SQL 2005, using a Common Table Expression and
the row_number() function.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]