I have two, and both involve helicopters.
In Battlefield: BC, there's an achievement for tagging a helicopter with an air strike. To do this you have to get out of cover, aim steadily at the chopper for a few seconds, then guide in the air strike manually while hoping the chopper doesn't move far enough to get out of your reach with the missiles tiny aiming radius. You can take damage during all of this. You cannot heal during all of this. Damn you DICE...
In Saints Row 2, getting a 4-star rating or higher spawns an attack helicopter with auto-targeting missiles that will instantly kill you on any difficulty in any vehicle other than an APC or another attack chopper. What they don't mention is that for the last few missions of the game they'll spawn choppers to attack you regardless of your wanted level. I died more times than I can count trying to dodge missiles while on a Sea-doo racing towards a yacht in the second to last mission (no checkpoints, you have to restart every time you die) before realizing that I could just hop on the Sea-doo, hop back onto the pier, and take the chopper out with a rocket (it doesn't seem to fire missiles when you're on foot) before heading off to the rest of the mission unmolested. Damn you Volition.
Though to their credit Volition did make the choppers in Red Faction: Guerilla much weaker and easier to kill. They're still a threat, especially to your allies, but one well aimed Nano Rifle shot to a wing brings them down quickly and satisfyingly.