DUH!!! All I need is a MAX/MIN on the application_id and it works. Thanks everyone! Here's my final query:

SELECT MIN(a.application_id) appplication_id,

d.brandname as drug,

dd.dosage,

...

Chris and GSquared - Thanks!

I had just tried something essentially the same to that approach and it almost worked. What I discovered however was that there were a couple applications...

I thought of that but the renew dates are arbitrarily set so the...

You're right. I'm developing locally on a 2k5 instance and when I tried to push the changes to our staging server (2k) it didn't work!

I suppose this is a 2k5...

Noel - It worked! I had to remove application_id from the partition, but that did the trick.

Thanks everyone for the help!

Sorry, I guess that MAX was a confusingly bad idea

Perhaps if I show you the result set I'm getting and the result set I'm aiming for it will help to...

Thanks for the response!

I gave it a shot but unfortunately that didn't seem to work. The application and tracking tables are 1-to-1, so selecting the top renewal date for a...

Turns out it was a typo. I was attempting to query an non-existent field in the table variable, and apparently that sort of thing doesn't throw any errors! Thanks for...

GSquared -

It's good (if not confusing) to know that this approach is solid. When you did it, did you use temporary table or table variables?

I'm going to go back and...

Jeff -

Sorry, I accidentally cut out too much of my original query... in each of those cases, it was matching a value in another table