In HL2:
Gravity Gun. Words can't describe the sheer joy of domination through detritus (although for some reason I never liked the Rock-It Launcher in Fallout 3)
In GoW2:
Lancer. Most games start you out with fairly weak weapons and give you better gear as you go. In Gears, they give you the most versatile weapon in the game, complete with the best melee capabilities, from the get-go. Once I got it, I never looked back.
Hammer of Dawn. I never dropped the Lancer through the entire Gears 2 campaign. Once I found the Hammer of Dawn, the only gear I changed around was the grenades and pistols.
In Fallout 3:
Hunting Rifle and Combat Shotgun. These were the two weapons that carried me through the game until level 15.
A3-21's Plasma Rifle. This replaced the Assault Rifle for me, and it isn't even automatic. (I like to call it the Goo Gun)
Gauss Rifle. I found this at level 15. it never left my inventory afterwards.
Ripper and Shishkebab. I couldn't have been the only person who wished for a hidden schematic that would combine these two weapons into an ultra-knife.
Fat Man. I barely used this weapon. I almost always died when I did. That didn't matter. This weapon brought the fear of god down on anyone who dared to attack from a safe distance (away from me).
Halo 3:
Gravity Hammer and Energy Sword. I REALLY like Grifball.
Shotgun/Mauler. The shotty/mauler + melee is the golden bullet that silences many a foe for me, at least until some bastard gets the sniper rifle.
Brute weapons in general. What can I say? I like the Brutes style. Their design process is to take someone elses idea, attach blades, possibly make it explosive, and if that doesn't work make sure you can hold more than one at a time.
Left 4 Dead:
Pipe bomb. It's always satisfying to see zombies swarm towards their own death. I just wish it would work on special infected.
shotty/autoshotty. definitive zombie domination.
while I'm talking about Left 4 Dead I'd like to give a dishonorable mention to the SMG. I tried to like it, I really did. But it was just so pathetic I could never enjoy using it, I would drop it for any other weapon the instant I got the chance, even if there was a witch sitting on top of the ammo table (true story).
