You won't be able to do it with one statement. This has got to be a classic problem althought I've never really read anything about it. I've ran in to similar situations before where I wished there was a way to do a modified "Group by" but instead of actually grouping the records you could specify which record out the group you are interested, like the top or bottom record of the group. Unfortunatly you'll have to brute-force this one as you've found one of the limitiations of SQL.
Rip Ryness
International Falls, MN