Halo 3:
Plasma Pistols. Normally shooting these things is completely useless. Their only use is charging them and shooting the charged shots. They can break an enemy's shield in one hit, and they home in. So why even have the option of shooting regular shots?
SMGs. They're just horrible. They use up way too much ammo, you have to practically be in the enemy's face to do any real damage, and they have a kick back so intense, you have to constantly push the aim analog stick down just to keep it straight.
Also in a game called .hack G.U. there are some weapons I got that I think are pretty cheap. I can't remember their names but one had an effect where for every critical you deal with them, they halve the enemy's HP. Another one had a 10% chance to reduce the enemy's HP to 10% of its max PER hit. In the original .hack as well, there was a weapon that had an even higher chance of reducing the enemy's HP to like 10-20% of its max or something.
In a game called Champions that I play, the weapons are totally unbalanced. You'll find that all of the cool looking ones have inferior stats compared to the more average, blah looking weapons. Scythes, cool looking swords like ones with dragon designs on them, all have crappy stats while ugly hammers, and plain old long swords have the best stats.
In Resident Evil 4 you can unlock a weapon called the infinite launcher after beating the game and collecting $1,000,000, which is a rocket launcher that has unlimitied ammo. Once you have this, the game will become a cake-walk and you will NEVER have to use any other weapon. Naturally the rocket launcher costs $30,000, takes up alot of inventory space, and can only be fired once, which means it's pretty dang powerful.
In Dead Rising there are countless objects that you can use as weapons which are completely useless. This includes toy swords, stuffed animals, water guns, frisbees, and many other ones. I guess they stuck these in so you could have a little fun in killing off zombies, but still, they're awful and you could never really depend on them at all.
Diablo II has so much of this crap I could never list it all. This is one of the most notorious games for having countless skills that become completely useless. What's the point of even having them if you know you're never gonna need them later on in the game. It just makes the game unbalanced and not as enjoyable as it could be.
And that about sums it up for my list of games. If I think of more I'll post them.