Partition Magic

Sometimes a T-SQL gal just has to lower her standards. ANSI standards, that is.

You're sitting there with a tough nut to crack and MS SQL Server just has such a … sweet… resolution for it that…

You just gotta admit that you're probably not ever going to migrate this database in this lifetime and that, if you did, Oracle probably has an equivalently sweet solution, spelled one or two letters differently, and you'll Cross That Bridge When You Come To It.

Case in point

I have a project where we're pulling a list of "candidates" for a customer service survey.  The rules are really complicated for selection, but the pseudocode for the query look like this:

SELECT Account, Email, ContactName FROM
   [ReallyComplicatedContactView]
WHERE
   [Millions of contact-filtering conditions here] AND
   [Lots more time-sensitive conditions here]
ORDER BY [priorities du jour here]

So now we decide that we also don't want to select more than <target number> candidates from any one Account, at any one time, for this survey. Bleh.

T-SQL 2005 to the rescue…

SELECT Account, Email, ContactName, AcctRank FROM
   (SELECT V.Account, V.Email, V.ContactName , 
           ROW_NUMBER()
                OVER (PARTITION BY Account
                ORDER BY Account ASC, [Priorities du jour] ASC )
   AS AcctRank
   FROM
   [ReallyComplicatedContactView] V
   WHERE
   [as before] AND
   [as before]
   ) XX
WHERE XX.AcctRank <= @TargetNumber
ORDER BY [as before]

That's almost too easy.