Missing SQL Feature: Select Top By Group

There's a common problem in SQL, where you want to take records in one table, and join each of them to a matching record in some other table that has the maximum value in some column, but you need data from several columns in that second table.

Let's see if I can come up with an example. Say you have a table of Sales Reps, and you have a table of the Sales they've closed, including the dates and amounts of sales. You want to write a query that shows the Reps' names, and the amount of their largest sale. Easy:

Select r.Name, Max(s.Amount)
From Rep r Inner Join Sale s On r.Id = s.RepId
Group By r.Name

But what if you want to include the date of the sale? Well, now it gets much more complicated. One common solution is to use a subquery that just gets two columns:

Select RepId, Max(Amount) MaxAmount
From Sale
Group By RepId

...and then join against that, and join the Sales table to that on the Amount. Where it falls down, though, is when the maximum is duplicated. What happens, for instance, if a certain rep happened to close two million-dollar deals as his top sellers?

You have to create an additional subquery that finds the maximum Id that matches each RepId and Amount, join on that, and then join on the Sales table to get the rest of the record (the Date field, in this example). The mess looks like this:

Select r.Name, s.Date, s.Amount
From Rep r
Inner Join (
   Select RepId, Max(Amount) MaxAmount
   From Sale
   Group By RepId
) MaxSales On r.Id = MaxSales.RepId
Inner Join (
   Select Max(Id) MaxId, RepId, Amount
   From Sale
   Group By RepId, Amount 
) MaxIds On MaxSales.RepId = MaxIds.RepId And MaxSales.MaxAmount = MaxIds.Amount
Inner Join Sale s On MaxIds.MaxId = s.Id

Wouldn't it be far more intuitive and easier if you could do something like this:

Select r.Name, s.Date, s.Amount
From Rep r
Inner Join Top 1 Sale s Order By s.Amount On r.Id = s.RepId

I found this suggestion on a blog by Raymond Lewallen here. It does leave a bit of assumptions on the SQL compiler, like having to do an order by subgroup on the items in the join clause; perhaps those fields would need to be specified (kind of like how you have to specify everything in "GROUP BY" whenever you use an aggregate, instead of it assuming that it should just by nature of being in the SELECT clause).


Dreamworks DVDs suck

Time to rant about Dreamworks DVDs. We just picked up Shrek the Third, and although not as bad as Shrek 2, it is still annoying.

My biggest complaint about Shrek 2 is that, when it starts, it launches this really long preview bit with Ben Stiller about Madagascar. But the DVD is authored such that you cannot press the skip-forward button or the menu button to skip it! Fortunately, fast-forward is not disabled. But it still annoys me that I can't instantly skip over it. I've seen this stupid documentary enough. I even have the movie on DVD; I don't need or want to see a promo to learn about a movie I've already seen.

Shrek the Third isn't as bad, although you do have to sit through the Dreamworks logo twice before getting to the menu, and the three previews can be skipped with the skip-forward feature (but not the menu button — a common annoyance with many DVDs).

If possible, I'd love to get a DVD player that does not respect the flags that say you cannot use features of the DVD player, like fast-forward, chapter skip, and menu. Nothing irritates me more than being forced to sit through logos and previews and copyright warnings. In fact, nothing makes me want to break copyright more than being forced to stare at the notice for several seconds every time I want to watch the movie, with every button press greeted with a "feature disabled" alert until the words are burned into my screen.

I wish I knew what this fascination was with the motion picture industry in taking away basic features from people, like DVD specs that include "do not allow" flags and TiVos that can't fast-forward through commercials. It's like they feel they have to balance their entertainment product with annoyance.



Well, in some cases. I do believe in using the right tool for the job, and, believe it or not, VB was the right tool for this job.

I had a need to convert an NT username to a full name. I found a lot of different, complicated methods of querying Active Directory, but the simplest answer was in VBScript:

v = GetObject("WinNT://domain/username")

As I looked and looked for an easy way to do this in .Net, I discovered that the GetObject function exists in VB.Net. Even better, after a quick trial, the same two lines of VBScript code worked in VB.Net!

Now, as it so happens, I was doing this in a Reporting Services report, which, for editing code, follows in the footsteps of SQL 2000's DTS ActiveX Task code editor — namely, VB in a textbox. What I discovered is, although it works great in the Visual Studio IDE, when I deployed it to the Reporting Services server (even on my local box, where every account is running as Administrator), it failed with a security exception.

The reasoning for this makes a whole lot of sense. Since you can access Reporting Services with a web browser and upload any .rdl files you want, if it didn't apply security constraints to that code, then anyone could upload any code they wanted to run as the Reporting Services account. Great, but how do you get around it when you need to?

The answer to that, I found (although not without a lot of digging), can be as simple as registering an assembly in the GAC, marking it with AllowPartiallyTrustedCallers, having it assert FullTrust permissions, and letting it do the restricted call. The report can then reference that assembly, and all is right with the world again.

Since I had to move this out of Reporting Services's code textbox, I thought I'd try rewriting it in C#. I still had a reference to the VisualBasic namespace to use the GetObject function, but the object i got back was of type System.__ComObject, and from there, I admit, I was stuck. Reflection couldn't get me to the properties, and C# doesn't allow for late-binding.

Could I have solved it in C# eventually? No doubt. But how long would it have taken me? This is my last day on this project, and I'd like to get things done; so if I can get it done by adding an assembly consisting of 15 lines of VB code in the GAC and call it a day, I have to wonder: why bother with anything else?

Imports System
Imports System.Security
Imports System.Security.Permissions

<Assembly: AllowPartiallyTrustedCallers()>

<PermissionSet(SecurityAction.Assert, Name:="FullTrust")> _
Public Class ADLookup
 Public Shared Function GetFullName(ByVal NTName As String) As String
  If String.IsNullOrEmpty(NTName) Then Return String.Empty
   Dim s As String = String.Format("WinNT://{0}", NTName.Replace("\"c, "/"c))
   Dim x As Object = GetObject(s)
   Return Convert.ToString(x.FullName)
   Return String.Empty
  End Try

 End Function
End Class

Reminds me of doing .Net 1.1 code, importing the VB namespace to get at simple functions like IsNumeric and IsDate (tryParse didn't exist for many classes then, and at the time I wasn't experienced with try/catch, certainly not enough to rely on it for intentional testing of data).


One page, a half-dozen files

Just had to continue on my SharePoint rant. Today, I wanted to add a new page to our SharePoint project. The goal of this page is to get a PDF file from a web service, and download it to a browser. Originally, I had this built in to the postback event of the web part that let you request this file, where the event set a property such that, in the Render method, it would then grab the HttpResponse object, clear it, set the Content-Type and Content-Disposition headers, write it, and end. This worked great, causing a download box to pop up to download the file, except there was a rather peculiar side-effect: after this, none of the events on the control would respond anymore. You could click on any link or button, and nothing would happen.

So, I figured this viewing page had to be separate from the existing page. Instead of a postback, it'd be a hyperlink with a _blank target and an appropriate pair of querystring parameters to trigger the correct report to open in a new window. So I created a brand new web part. Then I created the .webpart file, and the elements.xml and feature.xml file. Then update the manifest.xml file and the .ddf file. Finally, (after wondering where the heck my web part was and calling another developer over to give me another pair of eyes), I updated the deploy.bat file. The web part, plus six other files — and that doesn't include creating the .aspx page in the document library to host the web part (a process that I still have to do manually, since I don't yet know how to add it to a site definition or template or whatever the heck it is).

Let's compare this process to standard ASP.Net. Create the page, which creates the code-behind and designer files automatically. And then... oh wait, no "and then"!



I've finally had my fill of Mentadent toothpaste.

If you're not familiar with the product, it capitalizes on the idea that there's a dental benefit to brushing with a combination of baking soda and peroxide. The toothpaste is packaged in these rigid, two-chambered packages. You insert one into a dispenser, and as you push down on the top, two plungers push up on the two chambers, dispensing equal amounts of a blue and a white gel, presumably a baking soda and a peroxide formula.

That's the theory, anyway. In practice, it doesn't always work as smoothly as all that. Typically, because of an air bubble or something, at the start of a new package, one side will be short relative to the other, resulting in a couple days of all blue or all white. That, in and of itself, isn't too bad (except that it's usually the white side that's short, resulting in all blue, and the blue doesn't have the fresh taste that the white does).

The past two packages have been worse than normal, though. The former, the blue side had liquefied. Instead of a blue gel, out came a blue watery substance. I don't know if this was good or bad by itself — we did brush with it anyway, and for all I know probably ended up rotting our teeth with it — but when blue liquid started to show through the seams of the package (obviously designed to contain a gel, not water), it had to go, barely used.

The latter has seemed to defy laws of reality. For the entire life of the package, when dispensing the product, a good 3-4 inches of blue gel will come out for every half inch of white. I can understand this happening if they were filled unevenly, at the start of the package, but I would think at some point, eventually, it would even out. Unless I've been applying uneven pressure while pushing down on the dispenser — but since the thing is almost empty at this point, that kind of uneven pressure would eventually result in something like a 30° tilt.

The hassle just isn't worth it anymore. Sorry, Mentadent, but I just want a tube of toothpaste. This is just too complicated.


You have two weeks to correct our mistake

I got a letter from the Colorado Department of Motor Vehicles. It seems that when they issued the title for my Toyota Prius, they gave it a fuel type of "Gas", whereas they should've given it "Electric/Gas". I wouldn't think it matters much; technically, the thing does run on gas. The only energy I put into the thing is unleaded gasoline. The fact that it uses that gas to power an electric generator and, in the end, transport me about 50 miles per gallon of gas used is nice and all, but it does all run on gas. If I didn't give it gas, it would be dead. (Barring any conversion process that may allow me to plug it in, but technically the same could be said for any car; just that the "conversion process" may be much more involved.)

But if the Colorado DMV wants to make the distinction, I have no problem with that. What I do find a little interesting is that, along with the convenient "no postage necessary" envelope to return my title, they gave me a deadline: "Please return the title to this office in the enclosed envelope by May 13, 2008." I've owned the car for four years, and now it's imperative that I get this done within two weeks? What would have happened if I were on vacation?

Not that I'm too bothered by it. The two week deadline might just be an arbitrary date they put on there just to get me to return it. And, that's probably not such a bad thing. If there were no deadline, just "return it someday", "someday" could easily turn into "never" as it gets put off indefinitely.

Still, the implied "Hey, we screwed up, now hurry up and act to fix our mistake" almost makes me want to wait until May 14th to send it off.

Global Warming - Lastest Excuse for the War on the Family

Pretty interesting article on how the pseudoscience of global warming is being used in a renewed attack against families and religion. I'll have to keep my comments to a minimum, because not only does the author already make plenty of points clear, I don't think I could comment further without wanting to tear into the lefty environmentalist whackos who actually believe that tripe.

I love the author's last paragraph, though. Reminds me of the Mystery Science Theater 3000 short "Mr. B Natural", when Tom Servo quips, "Meanwhile, the Midvale police visit his locker to find out why they call him, 'Buzz'."

I always found it interesting (and sad) how words get twisted. Instead of perversion, it's "progressive". Obscene is "adult". And conservative faith and belief is "backwards", "Puritanical", and even "dangerous".

I've said it before. Some days, I wish the Second Coming would hurry and get here to put an end to this madness.