For a somewhat useful useless weapon, the Bullseye from Resistance (FOM at least.)
Sure, a 70 round mag, instant reload and insane firing speeds. Too bad you'll never hit anything.
Bullseye tags work well enough though. It's just too useless and too common, although it's better than most others on this list.
M4 in Prototype.
Fists in Doom.
Vanilla Revolver in Metro 2033. It's too slow to bring down mutants and too weak to kill humans and stronger mutants. Mine's got a scope, laser sight, silencer and stock, so it's an ok sniper rifle. But it's useless normally.
Pretty much everything in Bioshock. Pistol has little ammo and slow firing, machine gun can't aim, grenade launcher gets saved for later, crossbow is too slow, chemical thrower has a slow reload... The Shotgun and Wrench were the only useful weapons in the game.