Shotgun from Blackthorne (sound, how it works... upgrades... punch... pure awesomeness)
Pistol from Blade Runner (Westwood game) - heavy pistol with great punch! Perfect weapon - hevy but if you get use to it you cant life without it! Also it was a .223 pistol from Fallout but original is a Blade Runner gun.
Lawgiver from Judge Dredd (this gun was sooo awesome! From Judge Dredd mod for Half Life to Judge Dredd: Dredd Vs. Death!!!!! it was useful in every single situations!)
Lasgun (from many games from W40k world) - perfect for every situations. If run out of charges you can get some easily. If not put your mag into fire - it will charge up.
Plasma from DooM → much more useful than BFG according to me. Much more versatile. Faster, not so destructive (you can use those barrels later with more enemies). Wont eat up so much ammo.
And those are just few I can figure out now. I think in most games Assault Rifle is my fav for its usefulness in most of situations.