So many to choose from, so I'll list a few of the best in no particular order.
Golden Gun: no 007 game is complete without it. It's "THE" gun of small firearms.
http://images2.wikia.nocookie.net/__cb20130520184011/nintendo/en/images/e/e0/Goldengun.jpg
Farsight XR-20: Such a fun gun to use, really hard to not love it (and hate it if you're on the receiving end). Could see through walls and vary distance while scrolling as fast as you want, or automatically track people but not as fast as they could run.
http://www.3dmgame.com/uploads/allimg/130510/153_130510101314_1.jpg
http://www.youtube.com/watch?v=RqvCqk5WDrQ
A Purple "Thing": In Grand Theft Auto San Andreas, you could get a special melee weapon from the locker room at the police station. It was purple, it was big, and it was thick. What the cops did with it we don't really want to know, but it's awesome to hit people with.
I will not post a link to a picture of it because it's probably not allowed. Find it yourself on google.
Lightsaber: Most Star Wars games have one, and if it doesn't there is a good reason why it doesn't. Still any good lightsaber mechanic is just plain fun.
BFG9000: The start of it all with ridiculous weapons in games.