the poison arrow in "killer quake" quake mod. I made so many people cry with that weapon. You shoot them once and their screen goes all goofy. You shoot them multiple times and they can no longer control their char and the screen does goofy things and then their arms pop off and blood comes out or some shit. It was awesome.
Rainbow 6 Vegas and Vegas 2 had remote detonated plastique packs. You could also put little laser sensors on them that would alert you when someone walked by, so you would know when to set them off. It was great fun.
I'm going to say the P90 in MW2, but that's only because I'm an overly huge Stargate fan and being able to use them in a game is supremely fun(We really need a Stargate game that is made kind of like Mass Effect, only using the Stargate instead of a ship). If I overlook my geek choice, I'm going to have to say the Flak Cannon from the UT series. The first weapon I always go looking for.