well most weapons in bioshock would qualify, only used the pistol and shotgun in that game seeing as how i upgraded those first by the time i got the grenade launcher it was completely redundant, also plasmids they completely cancel out the high end weaponry there, no need for an explosive when you can simply stun your target in place with lightning.
never used the fatman always too careful with the ammo thinking there would be better times to use it also it's prohibitively heavy.
(still love the concept of it though)
butcher's cleaver, the model for the player sucked and by the time you were capable of killing the butcher solo it had become obsolete, i just loved the idea of some warrior decked out in plate mail brandishing a freaking cleaver.
there's undoubtedly more from a multitude of other games, where either the model of the weapon or it's concept absolutely rocked but it was either introduced too late in the game or the weapon just plain sucked (stat-wise)
so yeah that's a small selection.