Kouhei from Tsukuyomi: Moon Phase; his total lack of magic power also means he's impervious to all forms of magic, which is by the way quite common in this series.
Orihime from Bleach has a nearly useless offensive power, but is probably the ultimate support figure; rather than just healing wounds, she "rejects" them (i.e., turns back time in a small area and makes it so that the wounds never happened). She can also create a near-impenetrable wall.
On the other side of the trope, she has a projectile that can cut anything in half, but constantly misfires due to an unwillingness to harm. However she's also confidently stated that she can 'reject' any and EVERY event on a target, until they simply cease to exist. She's also raised the dead, and could probably even reverse the hollowfication process if she tried.
Another facet of her power is explored when she and Ichigo search for Rukia throughout the Soul Society after the battles have ended - She has her Rikka search for her, too, showing that she not only has the rejection powers, she also controls six faeries that can run certain errands for her.
Sailor Uranus in Sailor Moon gets a nifty mythology trinket courtesy of a magic sword. As you might expect from a television show, the amount it's used by an owner who has less issues with lethal force is surprisingly low versus a rather effective but generic magic attack.
Kuwabara in YuYu Hakusho eventually gets a sword that can cut through DIMENSIONS. Logically this means he can cut through any foe regardless of how powerful they are, and playing this trope straight, as the only member of the lead 4 characters without super speed or reflexes, he near never lands a clean shot.
Later on, Chachamaru comments that she was concerned that she wouldn't have a chance to use her artifact because of this law. Said artifact is a Kill Sat which despite being shaped like a cat is more than powerful enough to take out an Eldritch Abomination.
Yajirobe of Dragon Ball Z is pretty useful to have around, at least early in the series. He's the one who cut off Vegeta's tail while everyone with super powerful lazer disk attacks got their rears handed to them.
This mostly doesn't come into play in Darker Than Black since the Contractors who get sent on missions and end up in the protagonists' way are, logically enough, usually combat-oriented, but nevertheless we get situations like, say, Hei as opposed to Havok: his powers can be blocked, but he can also use them to pick locks and fix his landlady's TV, while her ability to create vacuums isn't useful for much but wanton destruction, but it's really, really good at it. The individual Contractor's remuneration also affects whether they can get away with using their powers for lesser concerns.
Ten Ten from Naruto is an example of this. In a world of ninja, magic fireballs, demons and God Mode eye techniques, Ten Ten uses normal weapons at her disposal. And because of this she is practically useless, which is jarring as any one of her weapons could end a battle real quick, as it's been shown countless times that no matter how strong a enemy is they usually have to dodge a kunai flying at their face.
Part of her problem is one of her defining features is a 100% accuracy rate in projectile weapons... Considering the enemy is still VERY free to either block or dodge, and her enemies are, well, ninja, it goes as well as you can expect.
She gets much more love in filler episodes, as her many normal weapons are summoned from special seals, and this is expanded upon to turn her into a master of Hammerspace. She has, on one occasion, hidden an entire ship in one of these seals and colony dropped the thing on some baddies. She is basically the boring but practical support fighter, in that when the others have run out of flashy tricks, she'll still have weapons galore to hit you with.
JoJo's Bizarre Adventure. Much of the intrigue comes from how villains use their Stands' strange and convoluted powers to deadly effect (A Stand that reduces your memory capacity to three given facts? A Stand that reduces the target's age?). The heroes often have to struggle to defeat their foes using their much more direct powers (Cut anything, control flames, extreme speed and strength...) A clearly established rule is also that direct power is inversely proportional to range, which means that heavy-hitters are heavily restricted.
This trope is why in Tiger & Bunny the flame-powered hero Fire Emblem is consistently low-ranked in universe. Besides simply being a nice guy, Fire Emblem is one of many Corporate Sponsored Superheroes who catches criminals for public entertainment. As such, his impressive flame production/control is relatively useless, given the lack of purpose that it can be applied that don't involve killing or severely injuring others. Notably, the criminal-killing vigilante, Lunatic, has the same power (but stronger) and naturally finds it very useful for his purposes.
The X-Men had a rather egregious mix of this and When All You Have Is a Hammer when they made Psylocke, one of their team telepaths, over into an Asian Action Girl. She gained kung-fu, Le Parkour, and the ability to focus her telepathy into a 'psi-blade,' which would instantly short-circuit the nervous system of anyone she stabbed with it, resulting in incapacitation or, in rare cases, death. Unfortunately, the blade became the entirety of her heroic repertoire shortly thereafter. Combined with the Inverse Law, it made the poor girl look like the weak link in the X-Chain, and it took new powers to give her any sort of versatility or credibility afterwards.
This trope made considerable angst-fodder for Havok, since unlike his teammates his power couldn't be used for anything but lethal purpose. Any shot of his plasma either incinerates or, equally useless, is completely deflected.
DCComic'sLegion Of Substitute Heroes, the rejects and washouts of the Legion of Super-Heroes. Their powers were deemed useless or dangerous. The ones who best fit this trope were Infectious Lass (she gave people contagious diseases - but couldn't control when she used it, who got it, or what disease they got), Porcupine Pete (could shoot porcupine like quills, but only in every direction, hitting friend and foe alike), Color Kid (could change the colour of objects) and Stone Boy (turned into an immobile statue). Despite their potentially offensively devastating powers, Infectious Lass and Porcupine Pete, due to their lack of control were reduced to objects of humour, while Color Kid and Stone Boy, despite their powers being, on the surface, useless, had moments of awesomeness - like changing all Green Kryptonite to Blue, rendering it harmless to Superboy and Supergirl, or immobilizing Pulsar Stargrave by falling on him from a great height.
Like Wheeler below, the Fantastic Four's Human Torch has a power whose most obvious offensive use is too gruesome. He's only allowed to burn objects, never people (though he's been known to bluff Mooks with that threat). Fortunately, Johnny is very creative with his flame powers; he can always find a way to use them indirectly against foes who aren't scared off altogether.
Interestingly, impressionable kids are a problem for Johnny in-story too. He's been driven to 10-Minute Retirement twice by hearing that some poor, misguided fan has burned himself to death. (This was a genuine fear at Marvel; there's an urban legend that the Torch was left out of The Fantastic Four (1978) cartoon for this reason. Actually, the character couldn't be used because the rights were tied up.)
Villains with flame powers are a little less constrained, especially in recent years. The classic The Flash villain Heat Wave is burning people alive these days.
Inverted in the original Wanted comic: the protagonist is The Killer; his only superpower is being unnaturally good at dealing death. While this would be unworkable in an ongoing comic (Punisher aside), it's mighty good in a miniseries.
Lifeline as presented in the G.I. Joe comic books. He's derided and insulted by his comrades because he will not shoot people. However, he wins his allies over by proving he can toss mooks around with the best of them. Toss, not kill. Plus, the guy who can stitch up your holes while under fire deserves some respect (except for his bright red uniform. At least Devil's Due tossed that).
The others could potentially be pretty lethal themselves, but their magic is far more applicable to everyday use.
Mystery Men has a lot of minor powers used this way to great effect as their league of Q-list heroes foil the bad guys. The people with more traditional powers, not so much.
In the Teenage Mutant Ninja Turtles movies, Leonardo's swords are only useful for Flynning until he can get a good kick in to finish his opponent. Occasionally he's allowed to hit someone lightly with one. In the second movie he wasn't even allowed to draw them completely (the most he gets is them half out of their scabbards in a big panoramic shot, but he puts them back and fights unarmed for no apparent reason).
The Harry Potter movies seemed to employ this trope as well. While in the books, spells can have many physical effects on their targets, in the movies, nearly all spells are designed to knock your opponent back a few feet. Even the ones that weren't. This is most obvious in the second movie when Harry and Draco Malfoy duel, where both say 4 or 5 different incantations all with the same exact effect of knocking the other on his ass...
Averted in Chronicle, where the protagonists use telekinesis in virtually every way imaginable, including infliction of lethality and destruction.
Sherlock Holmes is a fictional example of this. He specialized in all the sciences and fields relevant to solving crimes (lethality), but purposely ignored all knowledges and skills that didn't directly help being a detective (utility) as "irrelevant". This is why Dr. Watson was indispensable to him, he had a good deal more "common knowledge" that Holmes needed.
Holmes also relied heavily on Watson whenever force would be needed. Several times telling him to pack his revolver or giving a gun to Watson.
More as a practical backup or "just in case". The books showed repeatedly that Holmes was not only a skilled fighter, but also had terrific physical conditioning. In The Case of the Speckled Band, he very casually un-bent a fireplace poker that a character had previously bent as a display of strength (and then told Watson to bring his revolver, just in case it escalated past a "fireplace poker bending contest").
There were also cases where Watson's medical expertise became significant, since although Holmes knows everything there's to know about pathology, and so on, he doesn't always know common habits or working methods of professional doctors, which may be clues.
In fact, the few stories narrated by Holmes himself, with Watson absent, are those that Watson would have solved first as a doctor.
May be a Shout-Out to Doyle's inspiration for the character of Holmes, who was the physician responsible for the author's own medical training.
Played with in Animorphs: The "walking Cuisinart" species the Hork-Bajir are revealed to have evolved all of the blades on their body, which would seem to be pointless outside of being a race of foot soldiers, so that they can harvest their natural diet... tree bark.
It's not as stupid as it sounds. Why? Because the trees from their homeworld range up to something like half a mile wide. That'd be some crazy thick bark.
Not stupid at all. Look at Therizinosaurus ("Scythe Lizard") with it's nasty set of foot-long sickle-shaped claws used for... eating leaves. They even look a bit like Hork-Bajir.
For elves in the The Halfblood Chronicles, males usually have far more magical power than females, but as it turns out, weak females have a lot more control, allowing them to do a wide variety of useful things by using very little magic to alter their surroundings, like plants (food/shelter), animals (taming/control), minds (rewriting memories), themselves (minor shapeshifting), and enemies (stopping hearts). Not as flashy as giant illusions, fireballs, earthquakes, and so forth, but it gets the job done.
At one point it's raining. Very inconvenient. The men all agree that it's too dangerous and draining to try to alter the weather patterns, and so they must get wet. The women alter the fabric of their hats into makeshift umbrellas and snicker to each other.
Like Kouhei below, Bink from Xanth was thought to have no magic "talent" (and was banished because of it). Turned out he's The Magician of Magical Invulnerability (Magician being the top caliber of talents). No magic may harm him, even his own (by revealing itself directly, and making him vulnerable to non-magic attack). This gives it a near sapience. (However, he is not proof against looking like a fool, or anything that does not directly or indirectly threaten his life.)
A version of the fire example is mentioned in The Tough Guide to Fantasyland in reference to a frequent idea that the first thing your callow apprentice-magic user learns to do is light a candle through sheer concentration (and they are considered weak if this is the only thing they can do). The author sardonically notes that it's a good thing no one in the real world has this "minor" power. No character is ever shown honing the ability so that they can ignite buildings and other people at will.
But in the very first book of the Myth Adventures series by Robert Asprin, Skeeve only gets the hang of lighting the candle just before his master (casually) mentions how handy it would be during burglaries. Garkin also mentions that Skeeve could light a fire to cause a distraction; Skeeve responds he doesn't want to risk hurting anyone -just as he realizes the previous comments were a set-up. A subversion in that Skeeve recognizes the potential of 'just lighting a candle', both good and bad.
It is also worth noting that Skeeve does hone this power, and in the very second book uses this power to attack his enemies with flaming arrows and to torch a military signal tower from a long distance. (In the comic book adaptation of the first book he actually sets Isstvan on fire.)
Numair Salmalin from Tamora Pierce's Tortall Universe books runs up against this. He's one of (if not THE) most powerful mages in the world, and he has to blow out his own candles, because his power is tuned so heavily to big spells that if he tries to magic it out, it explodes. He can still perform incredible feats of great utility, such as calling boulders from ten miles away to form a new wall, rearranging terrain to make more room for a refugee camp, or turning a guy into a tree, it's just mundane things that most mages take for granted that give him trouble.
Vlad Taltos often plays the utility-guy in the Jhereg series, where his associates have usually until recently been immensely more lethal than he is. It's practically routine for him to get knocked out in the first exchange of blows/spells, yet still be the one who can think outside the box and solve the dilemma that's got Morrolan and Aliera stumped. Plus, versatility simply comes with the territory when you're a multiclassed witch/assassin/mob boss/sorcerer/smartass.
In Harry Potter, just compare the results of spells such as Expelliarmus and Stupefy versus Avada Kedavra. Guess which tends to work better.
While the first two sure get used a lot more, the series has a pretty high body count, and most of the casualties died to Avada Kedavra. Avada Kedavra may be lethal, but unless you're 100% sure you want the target dead, it's useless.
Lampshaded by other characters who point out Harry's seeming preference for casting "Expelliarmus" all the time. By the end of final book, its plot importance in the series is quite large comparable to "Avada Kedavra".
One doesn't have to use Expelliarmus to win the wand, they just need to beat the person in a duel. Disarming counts as besting an opponent in a duel, but so would killing them, or even just knocking them out.
Hermione plays this trope straight. Of the Power Trio, she appears to be the weakest fighter. Defense Against the Dark Arts (the whole point of the series) was the one O.W.L. she didn't get 100% on (Harry actually beat her there) On the other hand, Hermione outshines both Harry and Ron at, well, everything else.
In Brandon Sanderson's Alcatraz Series, a major plot point of the story is learning to put seemingly useless Talents to use (for example, the Talent of being late all the time can be used to arrive late for your own death). Also, swords and dragon-pulled carriages are MUCH more advanced than guns and cars!
The Dresden Files: Harry Dresden and his apprentice, Molly Carpenter, have this going on with their respective focuses. Harry's very good at combat magic (he admits on multiple occasions that really power, but lacks finesse), which includes massive gouts of flame, wind, and force, and while he's got some skill with magical tinkering, he'll never be any good at subtle magics. Molly, on the other hand, is incredibly skilled with mind magic and veils, meaning that she's got a lot of non-combat potential... but somewhat lacking at working up the will to protect herself in battle, despite have more raw power than Dresden. At one point she she creates a massive-scale illusion of fog over water (which tends to negate magic) and hold it, while moving, and, in the process, seriously impresses Harry and Thomas with the sheer power involved.
The Archive/Ivy averts this trope. She's the living embodiment of all written human knowledge and, by the expert and subtle application of magic, holds off an entire team of Dragons with finesse beyond the comprehension of Harry.
Live Action TV
Maya from Heroes has a particularly bad case of Bad Powers, Bad People: the ability to make everyone around her faint, then die. That said, she subverts this trope and uses the power quite frequently to great effect thanks to her brother helping "cure" those afflicted before they die. Once she could do it solo though, she almost took down the unbelievably resilientMagnificent Bastard Sylar. On the other hand, Sylar's power is the ability to intuitively understand how things work. That doesn't sound so amazing until Sylar quickly figures out he can use it to learn other people's superpowers, making him one of the most powerful people in the world.
The show also developed the inverse law of utility and special effects cost. This is why you only see Nathan soar across the sky once, while Micah repeatedly took control of machines - much easier to shoot. Greg Grunberg (playing Matt Parkman) said his character had "the power of leaning", since that was the only thing he had to do to read minds. Not coincidentally, it saw plenty of use.
Sylar's heavy use of telekinesis both averts and supports this, depending on the plot. It's a tremendously versatile power, good for everything from opening locks to stopping bullets to sawing off heads, but he has an amazing talent for not killing main characters, considering that he's a homicidal psychopath who can kill you by wishing it.
In Buffy the Vampire Slayer Willow (by herself) isn't a good fighter (or else she will snap if using magic to kill), but her ability at research and supportive magic is invaluable.
Danny Farrell in The 4400 develops an ability that kills fifty perfect of the people he's within talking distance to and gives a unique magical ability to each of the remaining fifty. When he doesn't get to do that, the promicin (a fictional neurotransmitter in the show) builds up inside his body and causes him discomfort and eventually death. He begs his brother to kill him to put an end to his misery, which his brother eventually reluctantly accepts doing.
A Shadowrun Decker is incredibly useful whenever faced with a problematic computer system, but generally finds it hard to kill things without other tools. On the other hand, adepts and more militarily-focused characters are quite good at killing things, but will usually find said abilities less than useful when the prebuilt campaign assumes some non-combat personnel.
Unknown Armies features "skill penumbras", meaning that every skill - most of which are custom-made by the players - has a substantial number of uses. e.g. a player's Cap In Your Ass skill is used not only for gunfights but also for weapon maintenance and accessing the black market, depending on how much the GM is willing to let them get away with. On the flipside, this also means that players can use skills like Crazy Driver to attack. Additionally, high skills usually imply a measure of fame in a particular area, meaning that, for instance, a player with a skill of 55% in Jeet Kune Do would probably be well-known in the martial-arts circuit.
And despite this status he would still have a 45% chance of failing at any Jeet Kune Do, and a 4% chance of screwing up bad enough to cause serious injury or death. Gotta love the Percentile System.
The actual skill percentage represents how good you are at that skill while under extreme duress. As in, other people are trying to kill you and the environment isn't being much more friendly. So that's a 55% chance to succeed in a very bad situation. When it comes to practice or formalized competitions, your effective skill is going to be much, much higher and can be performed very easily, meaning a 55% skill is extremely impressive.
Yu-Gi-Oh! has several cards that fit the bill. To name a few:
Treeborn Frog. It's a 100 ATK (and DEF) monster that sucks amazingly in its native theme (Frogs), but when you control no Spells or Traps (hence why he sucks with Frogs, they love their Wetlands) you get to bring him back from the dead. Especially useful when you run a Monarch Deck, where Monarchs need an easy piece of tribute fodder to allow them to fire off their effects.
Emissary fromPandemonium, who embodies this trope by being a Level 7 Tuner Monster. Tuners, to say it simply, combine with one or more other monsters to form a Synchro Monster whose level EQUALS that of the combined monsters EXACTLY. Meaning that to summon a Level 8 Synchro Monster... yeah. However, by summoning him with ONE Tribute instead of two and halving his ATK and DEF, you drop him down to Level 5, which is a lot more usable.
Winged Kuriboh. He's an adorable little fuzzball with low ATK and DEF, who will protect your Life Points during the turn that he bites the dust. However, he also happens to have TWO Super Modes, LV9 and LV10, which live up to the term 'Super Mode.'
Sasuke Samurai. He's from a Konami game, and he's arguably the worst thing a guy on the defensive can run into short of Neo-Spacian Grand Mole — mainly because his 300 ATK is offset by being able to instantly kill face-down defense position monsters — face-down being the default position for a defense position monster, this presents a problem.
Neo-Spacian Grand Mole, a 900 ATK monster (in a game where anything short of 1000 is arguably weak, hence most of the previous entries) with an uncanny effect. Whenever he battles something, both him and his opponent are returned to their owner's hand. This means that anything in your path is effectively null and void if you can keep getting your Grand Mole out to do battle with it. Bonus points (and usually an easy win) if you have a solid attacker on your field and are using Grand Mole to remove the only defending monster your opponent controls from said solid attacker's path (needless to say, this tactic has been run into the ground, and is the reason why you can only have ONE of Grand Mole in a given deck).
Neko Mane King fits this trope to a T. With a combined ATK and DEF of zero, you'd think it's completely useless... yet, if your opponent sends it to the Graveyard by any kind of card effect (destroying it on the field, discarding it from your hand, milling it off the top of your deck), their turn ends right then and there. Lucky Cat + Brick Wall = This.
On the "Lethality" side of this scale, we have the infamous Blue-Eyes White Dragon, which serves no other purpose than to be pure, unbridled beatstick... especially if you upgrade to the Blue-Eyes Ultimate Dragon, which is a fusion of three of these things whose firepower is only matched by 2 monsters in the whole game... one of whom, Master Dragon Knight, is a further fusion of this monster and Black Luster Soldier (who, on his own, is Blue-Eyes White Dragon with a sword and shield). So yeah, Blue-Eyes goes from boom to bigger boom to even bigger boom.
Brought to an unprecedented awesome when you find the series of supporter cards specifically MADE to back up the Blue Eyes. The theme deck that centers around the Blue Eyes is a prime example. Paladin of the White Dragon can be freely tributed at the time of its summoning to bring forth a FREE Blue Eyes White Dragon into battle, Kaibaman with the same effect with no strings attached, White stone of destruction to add a Blue eyes to your hand and with the setup, it can easily be a free trip to unloading all three on your side of the field as much as possible. Kaiba was also quick to make his signature combo the Ultimate dragon split which means that on direct attack, it can deal 13500 direct damage to any player, a one turn kill.
Call of Cthulhu has a wide variety of skills, from Library Use and Credit Rating to Machine Gun and Pilot (plane). Guess which ones prove more useful.
By and large, that is in keeping with how most Lovecraftian protagonists get by, although a dismaying number of them do end up dead or insane, so following in their footsteps may not be the best default.
Newbie players of Paranoia probably think "Machine Empathy" is an awesome mutation to get. Veteran players ask for a spare character sheet so they can get started on their next character ahead of time.
In the Warhammer 40k roleplaying game Rogue Trader this can happen to the mighty Astropath psykers and the mighty Navigator mutants, these characters suffer during space battles where they tend to just wait.
Actually, both have powers useful in ship-to-ship combat, especially the Astropath. If you can't figure out why precognition is useful...then you're as useless as I am for not explaining it.
Exalted plays with this trope, as social interaction is actually played out as a form of combat - and characters who are skilled at "social-fu" can do things up to and including killing and/or converting armies by talking to them. Simple brainwashing is one of the most basic abilities of a social Exalt...
FengShui mostly ignores this, as the characters should at all times either be in a fight or getting set up for the next fight scene, and they should never be unable to advance the plot due to a lack of noncombat skills. It does turn up in the rules for Sorcery under the Blast shtick, detailing the varying forms "hurt somebody with magic" can take, along with the other things that Blast can do. Lightning can recharge car batteries, Steam open envelopes or fog glass, and so on. Then there's Transmutation of one substance to another, with the note that any attempt to produce stone, water, or so on to use won't work: "It's only good for hurting people and wrecking stuff." Which is almost an inversion.
Guards in the Metal Gear series tend to raise the alarm upon seeing dead bodies or hearing gunshots. If they find a sleeping guard, they just wake him up. This makes the tranquilizer gun with built-in silencer the most useful weapon in the game.
In Metal Gear Solid 2, this is true. In MGS3 and MGS4, guards that are knocked unconscious in any way (punched out, thrown to the ground and knocked out, or tranquilizer darts) will wake up and immediately call HQ to report that "He got me!" Every soldier in the area immediately goes into Caution mode, which makes sneaking harder.
Thus leading to the rarified precision shooting required to shoot out their radios... which generally works pretty well, until the game decides that it hasn't heard from a particular goon on schedule, and sends the area into Alert mode to figure out what happened.
At least in Metal Gear Solid 3, it was still true. A headshot with either the tranquilizer gun or the pistol will instantly drop a soldier. But, should your aim be less than perfect, the tranquilizer will just take a moment longer to knock the guy out, while the pistol will cause him to raise an immediate alert. The smart play seems to be to tranquilize enemies from a distance, and then murder them in their sleep. Or, less gruesomely, leave the area before they wake up or dump them in a locker.
This is acknowledged in the Thief games, in which gas arrows, which release silent knockout gas, are among the best weapons available in the game. While Thief guards do not distinguish between dead and unconscious bodies and raise the alarm either way, gas arrows require no sneaking-up-close, work even on alerted opponents, and cause a silent faint while ordinary arrows cause screaming and bloodstains. Some missions also forbid lethal force.
The MMORPG City of Heroes allows you to "arrest" villains by beating them into a pulp, slashing them with swords, freezing them, blasting them with radiation, shooting them with bullets and arrows, or plain setting them on fire. Amusingly, civilians are utterly immune to splash damage from these battles in the streets which makes one wonder just why they need to be protected in the first place.
Touhou has Yuyuko Saigyouji, with the ability to kill anything simply by inviting it to its death (actually "control of death", but it mainly manifests this way). Perhaps the ultimate example of this trope, as it is absolutely lethal and almost completely useless, especially when compared to other characters, and she doesn't even use it that much (in fact when she first discovered she possessed the ability she killed herself due to her fear of it). The one time in the games she even considers using it just supports this trope further, as it was against a character who couldn't die.
Star Wars game Dark Forces II: Jedi Knight gives the player the option to turn to the Dark Side and gain damage dealing force powers, or to stay on the Light Side and gain defensive and healing powers. The Dark Side powers don't actually deal that much damage, and the player already has a lightsaber and 9 guns. Therefore the Light Side powers are actually far more useful.
Certain attacks in the Pokémon games will be this. Mostly, the one-hit KO moves, which will ignore defense and always take whatever it hits down in one hit, but only has an accuracy of 30% when an accuracy of 70% is considered low.
Averted with the Surf and Fly HMs, which are strong moves for their respective types while also being very useful out of battle. With the additional advantage that HMs can be taught over and over again, which wasn't possible with TMs until Gen V, they are very common moves to see in teams.
The Mortal Blow Hidden Skill in The Legend of Zelda: Twilight Princess. If used properly, it is a One-Hit Kill against any unlucky mook. However, Link can't even hold his shield while preparing for it. The Ending Blow, which finished downed enemies, on the other hand is one of the most useful Hidden Skills, and the first one Link learns.
Most RPGs have this built in: the more powerful (and aesthetic) attacks cost more mana.
In fighting games, your character's Limit Break super attack can only be used after certain requirements are met (usually filling some sort of meter), and then can't be used again until said meter is refilled again.
Grab and grapple specials and super moves tend to be stronger than other specials and, on average, look more impressive. However, they usually have harder inputs and can only be done when your opponent is standing next to you, which can be very hard to predict and leave you wide open.
Street Fighter has interesting examples beyond the usuals for fighting games: Akuma's Shun Goku Satsu seems very practical - fast-moving, fullscreen attack, can't be blocked and extremely powerful to boot - but it requires an extended and iconic input sequence that at tournament levels can be recognised and dodged easily before it is completed.
On the other side of this trope, Ultras like Dhalsim's Yoga Catastrophe and Rose's Soul Satellite create slow-moving balls of damaging energy - very good for comboing, but... it's just a slow-moving ball of energy, in a series whose best known attack is the Hadoken.
General Donald Grey: You know, there is one nice side effect of my guards using tranquiliser darts: they seem to be less likely to miss their targets. Probably something to do with the value of human life.
Inverted in the Magical Mina sub-series of Tsunami Channel. Mina's sword magic and wind magic were designed for combat, and she enjoys using them that way, but she'll also cheerfully use her sword magic to instantly slice an apple into many neat pieces, and her wind magic to dry her hair.
Actually taken advantage of in thisMagellan comic. During a superpower combat exercise, a firebreathing cadet gives the protagonist a warning shot. The protagonist's ally points out that since her blasts are liable to kill the Badass Normal protagonist, she can't actually use her powers for anything other than warning shots.
The Freeze Ray of Doctor Horrible's Sing-Along Blog doesn't fare too well, but the simple fact that Horrible is willing to fire it on his nemesis makes it a lot better at its intended task than, say, the Death Ray. Admittedly the latter does kill someone...
Painfully obvious in the idealism of The Descendants. Vorpal, a fan of Lewis Carroll whose power is precisely what you'd expect from someone who says "snicker-snack" when using them, only gets to use the power twice 'on-screen' so far (and not particularly successfully). Nightshade and Morganna have roughly similar issues. Zero is a lot less effective against humans than demons or inugami, since freezing people in a block of frozen oxygen apparently isn't fair. Chaos's non-lethal air concentration control gets much, much more mileage, as does Occult's wide variety of shields. Kareem's astral sight seems like it'd be useless in a fight, as the man can not even see or exist on the physical plane like everyone else until very recently, but is the default way to take down the most invulnerable bad guys.
Tennyo of the Whateley Universe has some extremely powerful abilities, making her a Person of Mass Destruction. But she can't use those high-level abilities without risking killing innocent bystanders, turning the area into an irradiated disaster area, or ripping a hole in space-time. Oops.
Gotterdammerung is one of the more teased/bullied students because he doesn't fight back. What's his power? Disintegration — only with little to no fine control over just what he disintegrates, so against people it's either kill or do nothing. (He gets a bit better after managing to save some lives without having to kill anyone to do so during the Halloween attack of all times.)
Both averted and played straight in Worm. While this trope applies most of the time, the worst villains may have a Kill Order placed on their heads. Against REALLY dangerous villains anything goes, including a certain amount of civilian deaths as collateral damage.
The Elemental Powers in Avatar: The Last Airbender have lots of Mundane Utility. Earthbending can be used for commerce, construction, augmented mobility over earth at high levels, and cheap public transportation. Airbending has a good deal of combat evading uses and a few others, allows for godly mobility, and being amazingly non-lethal allows Aang to waltz around opponents. Waterbending can subdue opponents nonlethally, do anything Earthbending can using ice, heal, and at high levels attain strong mobility when in water. Outside of combat, Firebending, at least according to Jeong Jeong, is really only useful for keeping warm, cooking food, processing steel, and destruction.
This trope is also present with Sokka. In the first two series, he hit a fair few people with his club and boomerang (which is bladed or not depending on what it hits). However, he never managed to hit anyone with his knife or the sword.
Fridge Brilliance kicks in when realizing that Sokka is a Technical Pacifist, and (mostly) only uses deadly force when given no option. He is an excellent swordsman, but uses his blades for defense and as tools, taking enemies down with nonlethal attacks and various Indy Ploy schemes.
But it's averted in the new movie: Although there's no blood or anything, it's fairly clear that Leonardo, Raphael, and April are hitting Foot Ninjas with their swords.
Given that original comic books featured a lot more brutality. Having Turtles constantly be covered in scars, and having actually killing Foot with said weapons. And cutting off Saki's head.
Curious example from the 80's cartoon; after a couple of seasons, parents complain about Michelangelo's nunchuks, cause kids supposedly started to imitate him (remember the toy nunchucks covered in foamy? ...well simply they just remove the cover and beat the plastic out of each other), so in later seasons the producers replace Mikey's weapon with a rope/grappling hook thing. Talking about something pretty lame.
The thing is, a grappling hook weapon can be pretty lethal (Manriki-gusari might be based on it) if you consider choking and flinging it at a vulnerable spot. The real kicker is that the least harmful weapon to imitate, and most difficult to duplicate — the nunchuck — is removed, while swords, knives, and staffs are allowed to stay. Considering their expys would be kitchen knives and broom handles — easy enough to obtain and hurt someone with — sounds like a case of nice job breaking it, watchdogs!!
Nunchucks are pretty low tech—wood and rope will do—and a glancing blow from wooden nunchucks can hurt. Nunchucks are also the easiest weapon to hit yourself out of the four.
In the mid '90s X-Men cartoon, Wolverine was never allowed to slash anything that would bleed, while Jubilee could shoot fireworks at anyone. Gambit never threw his cards directly at people, instead throwing them nearby so the force of explosion would knock them down.
Charging a card to explode and then handing it to someone who doesn't know your powers is just A-OK, though.
In one episode of Wolverine and the X-Men, an enraged Wolverine, battling his Arch-Enemy Sabretooth to protect a little girl, pops his claws ... and uses them to slice off a tree branch he could use as a club.
The first time Batman meets Scarecrow in Batman: The Animated Series, Batman turns his back on Scarecrow to deal with his goons and Scarecrow pulls out a gun and shoots Batman in the neck. It seems entirely a matter of luck that it was loaded with a dart full of nightmare toxin instead of something crazy like bullets. Which actually makes sense, considering the Scarecow's MO.
Another Batman example, in the Batman Beyond episode "April Moon", Terry is up against three villains who fight with deadly prosthetic limbs: one whips metal tentacles from his wrists, one can encase himself in powered armour and one has chainsaws on his elbows and knees. Guess which of the three goes down without landing a single hit on the bat. Granted, the placement of the chainsaws makes their use in combat awkward at best — Kneejerk's primary function seemed to be slicing open safes and vault doors.
Samurai Jack only uses his sword against robots and occasionally an enemy holding a sword. This is likely because Jack is a Martial Pacifist and doesn't want to kill anyone except Aku.
Many of those "robots" look and act exactly like organic beings, to the point of patronizing bars/restaurants. Even the wild animals proved to be robots, suggesting that the "robot" conceit is just a way to squeak the show past the gore police. A lot of them sprayed oil from their wounds in an obvious blood parallel.
In Teen Titans, Beast Boy never gets to bite anyone while in T-Rex form. He does do a lot of trampling and charging around as a rhino or other animal, but his no-holds-barred form is oddly ineffective.
Nuclear weapons. Not one has been fired in war since 1945, and they mostly sit around gathering dust and quietly persuading other parties not to try to use theirs. Units whose primary function is to employ nuclear weapons generally sit out any conflict that actually brews up, and need to be protected at great effort. Conventional weapons, by contrast, are much less powerful... and are actually useful in a fight that falls short of World War III.
A bolt-from the blue attack was generally discounted. Their primary purpose was to deter a Soviet invasion of Western Europe. The perceived importance of tactical nuclear weapons is usually underestimated.