The Star Wars Customizable Card Game had Epic Events, which typically required not only that you use associated cards, but your opponent use associated cards; for instance, Attack Run depended on your opponent using the Death Star and you having Death Star: Trench and Proton Torpedoes. All were rare.
The Exodia cards. Get all five and win the game. Fine, but you can have only one copy of each of them in your deck, you can only hold onto 6 cards in your hand at a time so it gives you less strategies the more you get, and they're really weak on their own.
Gate Guardian. Useless unless you also have Sanga of the Thunder, Suijin and Kazejin (which are themselves pretty rare) and have them all on the field at the same time. And if you had actually managed to do that, due to the aforementioned monsters' effects it would be far more sensible to keep them on the field rather than waste them to summon Gate Guardian.
Pretty much any card that requires another card to play. This can be worse if the "summoning" card is a rare in of itself. Venominion and Venominaga are good examples, with one being Ultra Rare and the other being Secret Rare. Venominaga however can only be played with Venominion's ability (with almost no exception), so even if you did score this extremely-rare card, there was no way you could play it unless you shelled out the money for the other, or got lucky with the pack again.
The Japanese sets include a rarity called "Normal Rare" where, even if the cards don't appear to be Rare, they are very hard to find. Oftenly, cards that fall on "Normal Rare" are worse than your ordinary Commons. Justseeforyourself. Luckily (or not), the Normal Rare rarity doesn't exist anywhere else, and it's much easier to find these cards.
Unfortunately the English version of the game has them too, just under a different name, "Short Print".
Evolved (Stage 1 and Stage 2) Pokémon are mostly rare, and require basic Pokémon first. (You can get around using a Stage 1 with Pokémon Breeder (also Rare), but you still need the common basic Pokémon.)
Another example would be the super Awesome, but Impractical legends, and some rares are actually extremely weak and nigh unplayable, like early Magnetons, Pidgeot, etc.
Rarity seems to be more related to the Pokémon featured than the potential for playability. There have been instances in every tournament season up to the present (2010-11) of a player doing well with a deck using nothing above Uncommon, the second-lowest rarity, though such decks have never moved beyond the national level. There was one time a World Championship winner's deck used only 4 Rares out of 60 cards per deck. (There were at least three rarities above Rare at that time. And if you're curious, it was the Lunatone-Solrock deck.)
In the early days of Heroclix, this is applied to uniques like Juggernaut without the helmet and original-costume Cyclops. Adding keywords didn't help these guys much. And the newer sets have gems like Spider-Man villain The Spot, Mr. Miracle & Oberon duo, and Queen of Fables.
It's possible, but extremely rare, to randomly receive 100 silver ore as a drop from most monsters with drop tables. Otherwise, silver ore is a common item, and even a hundred of them are only worth a few thousand gold in total. The dragon spear is obtained through a similar system (an extremely rare drop from the same wide variety of enemies) and is very close to completely worthless.
Clue scrolls occasionally reward the player with rare and valuable items...and other times, you get a handful of mundane colored firelighters.
Anchors are the rarest items in Super Mario Bros. 3: every other world has a secret level where collecting an untold number of coins gets you anchor. All the anchor does is stop the airship from changing its place on the map if you die. In World 5 a very rare glitch can make the airship fly to an unreachable square, so it's probably most useful there.
There are plenty of rare but almost useless Mons: Tangela and Lickitung were this in the first generation, and subsequent games followed in their footsteps with such duds as Qwilfish and Dunsparce. However, Chimecho takes the cake here: In its debut games, it was located in a small area of a story location well off the beaten path, only had a 2% chance of appearing there, and was not used by any trainer in the game, so you couldn't use the Pokedex to find it. If you somehow got lucky enough to find and catch it, you learned that it's one of the weakest Psychic-types ever introduced, with mediocre stats and a terrible movepool.
Any shiny Com Mons, e.g. shiny Rattatas and shiny Pidgeys.
Supposedly, Farfetch'd was a deliberate invocation of this trope. In Red and Blue it could only be gotten by trading a more-useful Spearow, and while fine at lower levels, it wasn't an especially useful Pokemon once it leveled up more. It's a reference and parallel to a Japanese phrase "a duck comes bearing spring onions." Or a different interpretation which basically translates into "somebody who is easy to take advantage of"- much like how a more useful mon had to be traded for it in the first generation.
Most baby Pokemon are also this. Many of them require you to capture and breed their evolved form(s), sometimes after obtaining a rare item, and then carry an egg around in one of your party slots until it hatches, for a Pokemon that is ultimately weaker than the one you started with due to being unevolved. Even raised up baby Pokemon are often worthless, as statistically there's no difference between a Raichu that started as a Pikachu and a Raichu that started as a Pichu. However some Pokemon do have moves they can only learn in their baby forms (Wobbuffet, for example, can only learn Encore as a Wynaut) so there is some occasional use for them.
Played completely straight with Bonsly, which provides only one extra move to its evolution; Fake Tearsnote Lowers the Special Defense stat of the opponent two stages. Sudowoodo has one of the lowest base stats in Special Attack of any evolved Pokémon, and in the generation of Bonsly's introduction has access to a whopping FOUR moves that are based on the special attack stat.
A recent change to the game's method of generating stats for the special group that babies, and, ironically, Legendaries, Nidorina, and Nidoqueen all belong to at least provides a niche for them - all legendary and baby (that is, Pokemon that cannot breed under any circumstance) Pokemon are guaranteed to have maximum potential in at least 3 stats. If nothing else, they make breeding for that perfect competitive monster easier.
A set of berries in Pokémon Ruby and Sapphire were extremely rare, only being obtainable through eReader cards. Some of these matched consumable items already in the game. Being berries, however, they were easily renewed, so it offered some edge, at least. Some of these, however, matched other berry effects.
"Pokemon Xand Y" had not one, but two event-exclusive moves that do nothing when used.
The Goblin, Bomb and Mind Flayer summons in Final Fantasy IV and its sequel are dropped with 1/64 odds from the named monsters and are woefully underpowered. They're slightly more useful in the DS version and After Years, where they do more damage and cost less MP to use than equivalent black magic spells, and unlike the spell Break which can and often will miss, Cockatrice always works on an any enemy that's not immune to being turned to stone.
Nintendo World Championship 1990 is one of the rarest NES games in existence with an interesting piece of history behind it, considered to be the Holy Grail of game collections. However, as shown on The Angry Video Game Nerd, it exists as nothing more than a competition cart split between segments of Super Mario Bros., Rad Racer and Tetris. Moreover, there are two versions: the gray one, which has a fairly standard label on it, albeit in black & white, and the gold one, which has a small label made with an ordinary printer and stuck onto the cartridge with glue. The gold one with the cheap label is worth thousands more.
Back in the days of vanilla World of Warcraft, Molten Core's Sulfuron Harbinger encounter occasionally ended in disappointment when one of the items dropped was Shadowstrike. Sure, it had the gimmick of transforming into Thunderstrike and back. But it was a Polearm (so Priests, Mages, Warlocks, and Rogues couldn't use it) with inferior DPS to other two-handers (so Warriors and Paladins didn't want it) and no stat bonuses (so Hunters, Druids, and Shamans didn't want it either). No surprise that its nickname became "Vendorstrike", and later "Nexusstrike," as it became a reliable source of Nexus Crystals from disenchanting.
Thor'idal, the Stars' Fury, the legendary bow that had a very low chance to drop from Kil'jaeden, the final boss of the Burning Crusade expansion. While by no means a bad weapon, it's main gimmick was that it created its own ammo. However, this wasn't that useful since you could buy Timeless Arrows, the best ammo in the game, for fairly cheap and in large quantities, and Timeless Arrows + The Golden Bow of Quel'thalas, an epic bow that was far easier to get, ended up doing comparative damage to Thor'idal. Near the end of the expansion, changes to how Steady Shot worked with weapon and arrow damage actually made the Golden Bow a stronger weapon for Hunters, making Thor'idal an unnecessarily rare weapon for something that's not even really an upgrade.
Several named weapons in Fallout 3 and Fallout: New Vegas have unique appearances, occasionally special effects, and are slightly better than their baseline counterparts, but are underpowered compared to most regular weapons. You'll be happy to claim Pacer's straight razor "Figaro", but you won't be fighting any serious battles with it.
Beets in Kingdom of Loathing no longer drop, and when they did it was only on ultra-rare occasions when resting. As a result, in the player economy a beet costs upwards of about two hundred million meat. This is in spite of the fact that the beet does absolutely nothing: it has no auto-sell value, it can't be traded to an NPC for anything, and you can't eat it, cook with it, or make cocktails with it.
Team Fortress 2 suffers this due to the Crate Lottery. Within the game there are crates that contain items of various quality. It's already a lottery when you gamble with the crates for a valuable strange weapon or an unusual hat. To further increase the drama, there is a second subset of crates called Salvaged Crates. Within those is where junk status truly begins. The Salvaged Crate offers a chance at weapons that may or may not ever see a release in a regular crate or you can get a strange version of a weapon nobody really wants or use.
Any game with a loot system, such as the Diablo, Torchlight, and Borderlands series, will fall into this sooner or later thanks to the Random Number God. Particularly noticeable in Borderlands, where quests will often reward you with unique, named guns which statistically are nothing more than Vendor Trash.
Aetherial grade gear, found in chests inside dungeons or as rare rewards from guildleves. This equipment is equal to a crafted high-quality item of the same item level, but with random stat bonuses as if it were already filled with a materia, where normal crafted gear instead had empty materia slots. But the stat bonus is random: caster equipment can come with strength, or tank equipment that adds mana, or anything that has a lot of skill speed or spell speed. Worse, these items can not be traded, and thus it's more common than not for a guildleve Aetherial drop to be unusable by one's current class, and either vastly under- or overlevelled for the player's compatible classes. They can at least be converted into materia, and equipped at a lower level than their normal equivalents, though.
Some individual pieces are rare, high-level, and still not worth finding or buying, for mechanical reasons. The primacy of weapon damage and lack of good off-hand items meant that the Allagan Sceptre, a Black Mage one-handed weapon, was less useful than its two-handed equivalent until patch 2.1 powered it up.