I was recently was looking at which Retro console to go for and ended up with the SNES and think its a great purchase with lots of excellent games. With regards to other systems i would look at Saturn or Dreamcast both had excellent games (Shinning forces 3, Power Stones, etc) and systems were overlooked by a lot of people when they first came out. The only thing to be wary of with all 3 is that some of the old games can cost a bit, especially the Saturn. Many of the RPG i want for the SNES are over £30 which while not expensive doesn't make going retro necessarily as cheap as i hoped.
Personally i would avoid the N64, i dug mine out of the loft recently and while the Zelda games are still good other don't hold up so well (Goldeneye) and the graphics on the N64 also seem quite poor. Partly i think this is due to the poor quality picture output as for some reason the PAL Gamecube doesn't use RGB scart.
Another tip may be worth getting a foreign console as it will open up the choice of games you can get.