I like Metro 2033's generally underpowered 'Bastard' gun and the pnemo guns, which either suck or rule depending on your situation.
I recently got the Fallout 3 GOTY (on pc for only 35 bucks!) and am loving it, I have an unhealthy obsession with the bolt-action hunting rifle and the supressed 10mil.
I loved the magnums on halo 2, mainly because they were the only guns in the whole damn series that didnt so much have that annoying 'firecap' (pause between shots).
Halo 2's submachine guns I loved, just 'cos that 'burrrrrttt' sound is so sweet.
In STALKER Call of Pripyat i used the starting AKS 74U for a good two thirds of the game, upgrading it as I went turning it into the ultimate death machine, and on Borderlands early on I found this 2 shot revolver that did ALOT of damage and fired electric rounds, which was underpowered a few levels later, but I still used it anyway 'cos it was so darn cool!
I play the original Call of Duty on pc occasionally and am crazy for unscoped bolt actions and the M1 Carbine.
Oh yeah and the knife you get in Fallout after anchorage, with the spikes in the handle, I just love clearing entire towns using that and the stealth suit.