(WARNING: This post contains spoilers for the Legend of Zelda games.)
I've been playing a lot of Twilight Princess lately, and I can honestly say that it is a great Zelda game. It's possibly the best one I've played since Ocarina of Time, and it might be even a little bit better. It also made me notice an important fact about the Legend of Zelda series: almost all truly great Zelda games subscribe to the Zelda pattern.
The Zelda pattern is this: collect three things in three dungeons, go through a big plot twist (the "Zelda twist"), then collect twice as many things in twice as many dungeons. With one exception, every great Zelda game has followed this pattern.
The Legend of Zelda: A Link to the Past is my favorite Zelda game, hands down, and is the first to follow the Zelda pattern. I had it on the Super Nintendo Entertainment System, I have it for the Game Boy Advance, and I even got it on the Virtual Console for when I want to play it on a TV and don't want to find my old, dusty Super Nintendo. It is one of the greatest games for the SNES, and I personally would rank it as one of the greatest console games ever made.
The game begins with Link rescuing Princess Zelda from Hyrule Castle and then hunting for three mystical symbols in three dungeons so he can wield the Master Sword and defeat the evil sorcerer Aghanim. Once you do all of that, however, the game really gets interesting. In the Zelda twist, Link gets sent to the Dark World where he must explore seven more dungeons and rescue seven princesses trapped in crystals so he can fight Ganon, the true evil of the land. This "But wait, there's much more!" element gave Link to the Past its endearing appeal.
Ocarina of Time was another truly great Zelda game. It starts with Link searching for three mystical pendants in three dungeons that grant him access to the Temple of Time and the Master Sword. Once he gets all three and takes the Master Sword, he's sent to the future where he must then collect six medallions of the Sages of Light, so he can finally face Ganon in his true form. Once again, a small adventure becomes a much greater one, with the initial three-dungeon taste only whetting the appetite for the rest of the game.
Twilight Princess follows that pattern once again. Since this game is very new, I won't spoil anything, but suffice it to say Link must explore three dungeons to get three somethings, something happens, and suddenly he has almost twice as many more dungeons to explore. The Zelda pattern is complete, and players are happy.
Wind Waker half-way followed the Zelda pattern, so it was a half-great Zelda game. It was a good game, a fun play, and had a lot of amazing moments. Unfortunately, after the Zelda twist, it drifted off into a pair of dungeons (only two?!) and a weird Triforce fishing expedition. It was great up until the twist, and then it dropped the pattern and meandered.
There are, however, one and a half exceptions to the Zelda pattern that I will freely acknowledge. The only full exception is Link's Awakening for the Game Boy. That was a truly great Zelda game, even through it didn't follow the Zelda pattern at all. Collect eight musical instruments and awaken the Wind Fish in order to go home. All I can really say is that it's an exception to "the pattern."
The half-exception is the original Legend of Zelda for the Nintendo Entertainment System. It didn't follow the Zelda pattern, unless you count the second play-through, where the dungeons are remixed. That's fine, because while it was the first Zelda, it wasn't truly the Zelda that all others followed. Much as Symphony of the Night set the tone for all the Castlevanias to follow, Link to the Past, not the original, is truly the game that all other Zelda games follow.
Except for the disastrous Phillips CD-i titles, there hasn't really been a bad Zelda game. They've all been fun, well-made, enjoyable experiences with solid game play and clever puzzles. However, while everyone remembers Link to the Past and Ocarina of Time, few really think of Zelda games like Majora's Mask, Four Swords Adventures, or the Minish Cap. They were good games, but they didn't follow the Zelda pattern, and they didn't reach the level of greatness LttP, OoT, and Twilight Princess did.