Well, there's the Blood Sword in Final Fantasy 2. Terrible stats: poor accuracy, zero attack power, reduces your effective magic power by 100 (in other words, to 0). However, it has one nice special property: with each hit (and you can get multiple hits per attack, theoretically as many as 32 with Haste), it steals an amount of hit points equal to 1/16 of the target's maximum HP. Yes, this even works on bosses, hence why this weapon is considered so powerful. The one downside is that it isn't that useful against normal enemies, well that and the fact that it reverse drains undead.

Just watch out, because many enemies (including the final boss) have attacks with the same special effect of the Blood Sword; the presence of these enemy attacks contributes to what I see as the game's biggest balacne issue, the fact that heavy armor is worse than useless. (By "worse than useless", I mean that heavy armor can mean that you lose a fight that would have otherwise been winnable without taking damage, plus there's the frustration of always acting after the enemy, being constantly ambushed, and never being able to run away.)

By the way, if you're looking at the type of games that are commonly called "action RPGs", I remember finding the Blood Sword to be useful in Final Fantasy Adventure, at least if your Power is decent (in other words, not if you put all your level ups into Wisdom, which is a rather interesting way to play the game).

Yeah. I also really liked the Doom 3 version.

I remember first finding it in sarge's office....it was like christmas and my birthday both came early that year.

======================

Someone made it even better(imo) by making it do rail gun style fire....priceless it was.

Fallout 2 => Bozar

Doom series: Chaingun, BFG, Supershotgun
Quake II: Railgun
X-COM UFO Defense: Blaster Launcher
X-COM Terror from the Deep: Disruptor Pulse Launcher
STALKER: AN-94 (forget what its called in game, but that is what the weapon is based off of)
DUSK: Rivet Launcher

Obokan

For hilarity: Shark-O-Matic from Saint's Row The Third.

For gameplay fun:
F.E.A.R.'s HV Penetrator
Quake's Rocket launcher
Doom 2's Super shotgun
Dying Light's Ranger bow (once head-shot two Volatiles storming at me, the second mid-jump and it landed on top of me. The third got me, though).

While I don't play first person shooters, I have heard of a couple interesting weapons that have appeared in such games, namely the medi-gun and portal gun. Perhaps a character armed with these two weapons (which I believe are not found in the same game) could be reasonably effective, albeit incapable of doing actual damage.

In Age of Empires III: The Asian Dynasties expansion, you can use chakram throwers as military units.

Double barrel shotgun from Doom

Super gravity gun Half Life 2

Crossbow Deus Ex

Okay, so this is a bit of an exploit, but it's my favorite perfect storm.

Pool of Radiance: Ruins of Myth Drannor (which uses the D&D v3.0 ruleset) has a weapon called the Draedsword Daemoniac. (It is, of course, a two-handed sword. Just thought I'd point that out.)

It has almost the highest to-hit bonus in the game (+6). It adds +4 to the wielder's strength, which in turn increases the chance to hit by another +2. It does almost double the damage of the next most terrifying weapon (the Baneblade Moravian, for those playing along at home).

And then, if you happen to possess a certain item exclusive to the Monk class, the damage from that weapon is doubled. (Mutliclassing is fairly easy in v3.0, with minimal penalties.) On top of that, it crits almost 40% of the time, if you have the Improved Critical feat (which of course you do). And critical damage is in turn tripled.

Picture, if you will, regularly doing almost 300 hp of damage in a single blow, in a game where even the best weapons typically top out at around 75 (and average closer to 40).

I'm picturing it, and I'm smiling.

The medi gun is a 2 handed weapon, the portal gun can be worn and used on one. Outside the use of the game it's in, the medi gun is big enough to bludgeon someone with. The portal gun is capable of inflicting damage as well, just not through a conventional bullet delivery method. Use of both would be really cumbersome just based on the existing logistics of each.

Red Faction's Railgun
It's just so satisfying to shoot your enemies through walls thanks to the x-ray vision scope. If lucky you can also nail 2 or even 3 people that way. This was especially fun in multiplayer. Yay, legal cheating! ;)

Dark Forces's Blaster Rifle
Because pew pew pew in the Star Wars setting!