As with virtually every Zelda game, the ending of Skyward Sword (the battle against Ghirahim's "horde" onward) stuck with me the most. It's exciting, it's creepy; I laughed, I cried. It's great stuff.
That said, the whole game is chock-full of memorable moments. Here are just a few others:
- Talking to people in the Skyloft bazaar. Quite the characters they've got there.
- Ghirahim's monologue in the Fire Sanctuary.
- Battle against Koloktos.
- Not to parrot the rest of you guys, but, yeah, every scene involving Groose is just hysterical, particularly his arrival to the land below. The dude's definitely grown on me, to the point where he's probably one of my favorite characters in the series -- second only to Midna.