paladin181: EDIT: Has there ever been a character more useful early-game and more useless late-game than a Red Mage?
It depends on how the game is balanced. I note that Red Mages in both FF1 and FF3DS are still quite useful endgame. (FF3DS RM's can even use Excalibur and (if you're able to get it) Ultima Weapon, and get a small number of Curaga casts, which are usually strong enough; I like using one as a melee attacker and to help support my Devout if the Devout is busy casting a spell like Haste.)
As for useful early and useless later on:
* Mages in some Japanese Wizardry games. Fighter-types continue to improve at high levels, while casters stop getting higher level spells. Furthermore, hybrids can be just as good at casting as pure casters in the long run. (Wizardry 8's mage sometimes feels this way mainly due to fire resistance being common and Alchemists getting good high level spells.)
* Mystics and Monsters in SaGa Frontier. They can do decent damage early on, but without the powerful techniques that Humans get, they can't compete offensively later on. Hence, they tend to be only really good for MagicHeal and (if you're able to get it; most Mystics can't, and I believe it's a 1/64 chance for a monster to learn it from a Unicorn) LifeRain. (Mechs get PluralSlash and can easily reach 99 STR.)
* Tosh in Arc the Lad 2 might qualify. His thing is physical attacks, and by end game you'll be outleveled to the point where your regular attack is not going to hit at all; hence, you're better off using someone with nice AOE skills. (Tosh at least does get a skill that can inflect Weaken Enemy, which makes its target hitable, but Arc is better for that.) (Also, worth noting that Diekbeck is always level 1, and therefore won't ever hit with a normal attack, but at least they can get some special equipment to get good stats and some good healing spells.)
* In the SaGa 3 remake, bow users. You can get a Mythril Bow early, and it's quite good for that point. However, bows do not scale with stats, and further bow upgrades don't get enough power to keep up with everything else. (Fortunately, you can just switch to a different weapon; Espers, who are the race with bow proficiency, also have proficiency in rapiers and all types of magic.)