To me, there are two Zelda games that will always stick out in my mind for being awesome.
LoZ: LA - The first handheld game of the series, the story blessfully deviated away from Princess Zelda to focus more on our brave (and vaguely homosexual) hero, Link, and his attempt to escape from Koholint Isle. While the dungeons were mostly typical - with a couple of them bordering on downright cruelty for difficulty, BITE ME EAGLE TOWER - and it still involved the traditional "go kill things to get 8 Magical Items" storyline, they at least tried to change things around. Link's Awakening also gave us the first Trading Sequence in the series, starting with an innocent Yoshi Doll and ending with the Magnifying Glass, an item that allowed Link to passively find hidden things in the world, including the moblinesque critter that would give you the Boomerang, which was easily the most OP item you could get ahold of until the Magic/Flame Rod in Turtle Rock. Plus, I think we can all agree that the Tal Tal Heights background music and the Ballad of the Wind Fish were epic victories. The GBC remix removed a couple things that I was glad to see gone (like the blink across the screen and glitch trick), and introduced a brand new dungeon with fairly decent rewards.
LoZ: Majora's Mask - I remember this game getting a LOT of flack back when I first got it, but in my opinion, Majora's Mask is the better of the N64-era games, and really shows what OoT could have been. At only four dungeons (and thus Four Magical MacGuffins), it was certainly shorter than the other games if you wanted to just zoom through to the end of the game, but the true strength of the game is in the sidequests and NPCs. You just don't see this kind of development in Zelda games anymore; Majora's Mask attempted to make every character seem important and real. Be it the owner of the Honey Pot Inn, Anju, or the family that lived in the Music Box House of Ikana Canyon, they made sure to make every character have a purpose, and it almost always made you want to help them. While it was admittedly frustrating to have all of your hard work go to waste when you reset time, it was also an incredible way to remind you (the player) that you were not just doing this to settle things with the Skull Kid, but to save the people around you, as well.
As was mentioned, the clock is perhaps the biggest detractor to MM; it was more than a little bit difficult to do even the stuff you HAD to do in the game, let alone find time to save a failing relationship, stop aliens from abducting the cows of Romani Ranch, or even help two dancers perfect their routine! And even WITH the song to slow the passage of time, it can feel like you're being rushed, but...that's what I loved about it. It felt more realistic than a lot of other games, where you can spend three hours juggling rabbits off a cliff while the main love interest hangs on by a thread to life. So, the clock wasn't that big of a pain.
The Great Bay Temple and Gyorg (the third boss) specifically? It can pucker up and make friends with my bouncy, swollen, navy blue...
...I've been informed I can't finish that thought. But yeah, hate Great Bay Temple. I dislike it more than the OoT Water Temple, Jabu Jabu's Belly (and swimming in general with the Mermaid Suit) from Oracle of Ages, and that stupid puzzle to bring the fourth floor down in Eagle Tower combined. And I downright HATE that puzzle in Eagle Tower!
So yeah, MM is #1 on my Favorite Zelda Games list, with LA (DX) being second.
[i'"Any sound can shake the air. My voice shakes the heart!"[/i]-Sho Minamimoto