In no particular order:
1. The Precast/Paralyze/Explode/FlameStrike/Hally combo from Ultima Online, pre-UOR era.
Not really a weapon, more of a move, and while some luck was involved, it was largely skill because it was about when to pull off this move. In a duel, if you did it right at the start, you're doomed. If you do it without properly whittling them down to just the right level, and having just the right amount of reserve to finish them off, you're doomed. Yet, in most high-end duels, this was really the only way to end the fight. Big risk, big reward, that makes a move or weapon fun.
2. The sniper rifle from Sniper Elite
This game was not played by 99% of the gaming public, and it's really a shame because it was ridiculously awesome. Like #1, the gun required some skill to use - it was the first time I held my breath in the game and out of the game, and really felt like I was doing it (probably because I was). The key was the immensely satisfying bullet-cam and subsequent cranium explosion from a well placed it - all the more rewarding on a moving target with full realism enabled, having to deal with wind from over 300 yards away - and hitting them square in the eye after a dozen attempts and failures.
3. The assault rifle with the barrel shoved up a cat's ass from Postal
Silly, but it was so irreverent and hilarious, it made a standard and boring weapon great.
4. The handheld (non shootable) tazer from Postal.
I gleaned much satisfaction from simply walking up to people and tazering them in the neck until they pissed themselves.
5. The spinning thing from Painkiller
Just awesome, deserves a spot since it's the only true Cuisinart of gaming.
6. Any car from Flatout
In a game where ramming the other cars is the point, and it's done right (unlike Burnout) and the reward is seeing someone fly out of the windshield and scream to their deaths is a plus in my book.
7. The aerial bombs from Battlestations: Pacific with follow mode (hold mouse btn 1)
While the final explosion wasn't particularly satisfying - it lacked the feeling of heavyness and massive damage of other bombs, in Pacific you can hold the fire key and it will follow the ordinance to its target. This, combined with the lining up of the crosshair and having to lead your target, is really satisfying to get a hit.
8. Any torpedo from any sub in Silent Hunter 4 in near-full-realism mode
When you have to stalk your prey, set up your position, and then read a trigonometry primer just to understand how to use it, and practice for a week before you can even come close to scoring a hit, and spend a minimum of 5 minutes setting up instruments before you can land a hit, you feel a real sense of reward when you sink a massive ship as a result, killing hundreds. Additionally, the penalty in Silent Hunter for daring to actually fire your weapons is sometimes very steep, forcing you to pick your shots very carefully. If you reveal yourself, you'll find your sub dodging depth charges for the next 4 hours. Name any piece of ordinance from any game that puts you on the defensive for that long after using it.
9. The AK-47 from Counter Strike
The perfect weapon requiring a hell of a lot of skill to beat other players with it. Instant hit, and the first round hits *where the damn crosshair is* the first time, virtually every time.
10. The knife from Counter Strike 1.6
Simply because it's A.) Always funny to kill someone with it, especially while jumpcrouching, and B.) Once again, it's hard to actually do, yet somehow still having *real value* while playing a highly competitive game.
Conclusion
I realize I probably like weapons where you can follow the bullet/ordinance to its target to bathe in the gory results of your hate for the enemy, and weapons that are actually challenging to use (excluding #s 3,4,5, and 6 above, hah!) - not hard because the game interface is broken, but challenging because it makes sense for them to be challenging. I like being challenged in games, but I also like gore and enjoy having a laugh.
I could probably name 10 more from games that are actually older, but these are just the first 10 that come to mind. Thanks for reading. Go stab yourself now please.