I've got a few but the 5mm Hunting Rifle is one of the best. Cheap and plentiful ammo, 15 shot clip, sniper sights, faster action when modded and it can do a lot of damage.

In which game?

As for me, I like:

Half life 1: Magnum, Gauss Gun, Crowbar(less for utility and more for nostalgia), grenades.

Half Life 2: Gravity Gun(basically can use anything loose as ammo or a shield), Combine Assault Rifle, Rocket Launcher, Bug Bait.

System Shock 2: Assault Rifle(due to stopping power), Grenade launcher(variable ammo types), Wrench(though also crystal shard if/when able), Stasis Weapon, Worm Launcher(works well in the endgame).

Duke Nukem 3D: Shrinker(expander as well...both are fun to use), Mighty Foot(unlimited uses plus nostalgia), Chaingun(the one with three barrels), and both the RPG/Devastator.

Those are just some of my favorites.

Borderlands 2: Moxxxi's Good Touch and Moxxxi's Bad Touch, a pair of high fire rate SMG's. The unique thing about them is, if you play with a controller, they slowly start to vibrate out of control and won't stop. I thought it was a game bug and was restarting the game and rebooting the Xbox and still they were vibrating.

Then I stopped to think about it, who gave them to me and exactly why they are called good touch and bad touch and it all made sense. They are meant to vibrate...

No other game makes you love your guns like Borderlands.

There's something so profoundly satisfying about the nailing of people, creatures, any opponents to walls or any surface that for me it's definitely weapons that provide that experience.
Such as Painkiller's Stake Gun, F.E.A.R.'s HV Penetrator or Amid Evil's Star of Torment.

Amid Evil's Celestial Claw probably is the weapon with the most ingenious concept/lore behind the way it functions.

I also loved those(the bolded ones).....it was fun to stick enemies to walls or surfaces in both games.

The Varmint Rifle from Fallout: New Vegas.

Very reliable for a starting weapon and it can be modded into a silenced sniper rifle with night vision. With the addition of AP ammo you could use this weapon reliably for a very good chunk of the game depending on your play style.

The VK-12 in F.E.A.R. - You don't just kill your enemies, they become blood fireworks that often blossom in slow motion when you fire that thing.

The riot gun in the original Shadow Warrior - It's like a double-barreled shotgun, except someone saw your Christmas wishlist and gave you two more barrels because you'd been extra nice that year.

A noble weapon: 2 handed shovel.

Mantis Sniper Rifle - Mass Effect 2 and 3

Starting weapon but a balanced beast, with mods available in ME3 it can go toe to toe with just about any other Sniper Rifle in the game. Nothing beats getting a head shot through the mail slot of a shield then getting the guy behind through the shield slot as well.

Mantis was good, but Incisor was great for quick shots and the Anti-Tank one was good for single hit kills.
(Equip the anti-tank one with explosive ammo and BOOM....no more bad guys)

1. Grenade Launcher (Shadow Warrior, 1997)

An odd choice to be sure, but this might be my favorite weapon. It may not be flashy, but it always felt right. Not only is gibbing enemies around corners incredibly satisfying, but I love its sound effects (pow...clank-clank-clank...BOOM). It's without a doubt my favorite bouncing grenade type of weapon.

2. Rocket Launcher (Unreal Tournament, 1999)

This has long been my weapon of choice in this game. While I enjoy using quite a few of the other weapons, the Rocket Launcher has always proved the most versatile in my hands. With quite a bit of variation in its modes of fire, it really helps to cover for my lack of accuracy. Even if a Godlike bot has my number, I can count on the Rocket Launcher to even the odds for me. The only downside is that the secondary fire (grenades) isn't nearly as awesome as its Shadow Warrior counterpart.

The rocket launcher and the railgun in Quake 3 are two of the best weapons ever made in any video game. Nothing gimmicky or spectacular, just perfect mechanics from a gameplay perspective.

I have used Rocket Launcher and Missile Launcher but they tend to have adverse effects on looting bodies and searching around for goodies that were neatly stacked on a table/cupboard because of obvious reasons.

Yeah in some games(Bioshock series iirc/etc) burning or blowing up/shattering bodies makes them unlootable, sadly.

Lightsaber from Jedi Knight 2 : Jedi Outcast
Gravity gun from Half-life 2
Flak cannon from the Unreal series