The AMR in Fallout New Vegas is a favorite. "A Light Shining in Darkness" is also pretty badass for executions and the like, though it's not particularly powerful.
10mm Penetrator (Perhaps the most Freudian gun of all time) from F.E.A.R made me giggle like a little school girl the first few times I used it. It's always fun to hit people from below with it. The shotgun in that game was also a lot of fun, but I always like shotguns.
Some of the weapons from Borderlands were fun, at least until I leveled up and they became little more than overrated massaging devices. Hard to stick with one gun in that game.
Overall though I'd say the AMR though. I've always had a fondness of sniper rifles, and one that can turn targets into hail of flaming meat chunks in a single hit warrants extra recognition. It's a weapon that blurs the line between "fighting" and "smiting."
The katana in Conker's Bad Fur Day, oh the number of Tediz heads I've severed! The magic boomerang in any of the LoZ games that it's present in (The Gale boomerang in TP was also pretty bitchin'), aaaaand Maya, my level 100 Dragonite (Hyper beam, Flamethrower, Ice beam, Thunderbolt)(Atk-336 Def-249 SpAtk-264 SpDef-264 Spd-221).
Long range, Sniper rifle.. MSR or Barrett .50BMG, while I have my preferences I normally go with accuracy over range or power.
Mid-Close FNP90(when available), or DMR, M14.
Sadly most shooter game's selections of rifles... is lacking...
In GRAW FS i normally always go with either an Sniper or DMR and FN90 in semi auto(disappointed they don't have burst shot on it).
Halo(varies heavily depending on map...) normally a sniper and dmr(carbine, br, dmr, ect..)
Now this is a tough one, but... Any of the Unreal Tournament rocket launchers, Doom's super shotgun, Bioshock's shotgun with electric buckshot, or Borderlands' Combustion Hellfire. That's not even getting me started on MW4's UAC2's.