PC? X-COM UFO Defense?
Console? X-COM UFO Defense for PS1
Seriously though, that game will never be topped since future of gaming appears to be Farmville or similar shit.
Runner-ups, RPG-wise? Crystalis for NES, Drakkhen for PC (and SNES), Technoclash for Genesis, Knight Lore for ZX Spectrum.
Notable mentions: Global Defense for SMS, Wizardry 8 for PC, Vapor Trail for Genesis, Daggerfall for PC, Thrilla's Surfari for NES, Battletoads for NES. [Console games listed here are extremely difficult regardless of genre, hence their inclusion.]
Special mentions: Mach Rider for NES, Ikari Warriors for NES. [These two games feature bugs with identical results. Both games have no end and are unbeatable. As in, you just keep going, playing, playing, level after level.... A kind of humbling yet inspiring experience. Like playing Mortal Kombat in standard/story mode, except if there were an infinite number of challengers. After a while all the secrets of the universe start revealing themselves. Well, that's probably the effects of dehydration and exhaustion.]
...
..
.