1. SNES - streetfighter 2 / 2 turbo were simply the most iconic games of the early 90s. Admittedly, you could argue Mario is more 'iconic' but nothing unified all the male kids and teens of an era they way SF2 did. The original Mario Kart was simply magic. Those two games are perhaps the two best console games of all time.
2. Neo Geo AES - surely relative to its time of release, this was by far the most powerful console. Literally bringing the exact arcade experience to the home...the first arcade perfect machine. Massively expensive and elitist. The games had such character. King of Fighters '98 is one of the best beat 'em ups of all time. The Metal Slug series is just a classic. The Last Blade games are pure elegance. No console before or after could ever hold a torch to how good this looked...the perfect design aesthetics of the case and the MASSIVE cartridges were the coolest things to behold.
3. Sony Playstation 1 - Perhaps the biggest step up in console history. The leap to truly 3d gaming had us all in awe back in the mid 90s. Games like Tekken 1/2, Wipeout, Total NBA, Ridge Racer, Destruction Derby, Loaded in it's early days just took the world by storm. What a plethora of a catalogue it developed by it's end.
I suppose N64 deserves a mention for having two true killer applications. Goldeneye was perhaps the console game that unifed gamers the most after StreetFight 2 / Turbo on the SNES. N64 also had Mario 64, which gave rise to true free roaming in a game world, amd many critics consider to be perhaps the best game of all time (I wouldn't go that far.) Mario Kart 64 was a worthy succesor to the original. The gamecube Mario Kart : double dash was not a worthy successor due to generally bad artwork and map design, and music, however its driving machanics were the best by far. I'd say the most important game of all time will always be Doom, but that is on the PC and this thread is about consoles.
edit: just wanted to make an additional comment regarding those who considered the PS3 in their list...do you even THINK before you post? I can't see a single valid argument for its inclusion, other than technically it has the highest hardware specs of any console, which can in no way jusitfy it as one of the greatest ever consoles. You have to consider the console as a whole: games, looks, power, wow factor, impact on the gaming community, impact on the mainstream.
The PS3 is a console that has thus far disappointed on many levels in its SHORT lifespan. We are talking ALL TIME here, and you lack the foresight to look beyond your most recent purchase just because the graphics are good due to technology. There is not a single available title on the PS3 which is a must buy, and none of the highly anticipated future titles are proven to become classics until they are released and live up to expectations.
I am guessing the people that listed PS3 are quite young guys who missed out on the classics of the 90s. To be honest present day gaming at its highest level is mainly on the PC, with all consoles just battling for 2nd place. So I doubt there will ever be a console that affects the gaming world the way they used to back in the 80s and 90s. All a console tries to do anymore is emulate a PC (except for the Wii.) So maybe from the current crop of consoles, the Wii stands the best chance of being the one that impacts the community the most when looking back in a few years. In my opinion the extent of the impact the current gaming consoles have on the community and mainstream lives and dies with the marketing, not with the games or the machine.