The Ocarina of Time was probably the first game I completed, ever, and had by far the most exciting and rewarding end sequences that a, what, 9(?) year old could have expected, lol. Majorca's mask didn't do it as well for me, maybe because getting there was a bit more haphazard than in Ocarina of time.
Next best was Halo 1 - completing that with my sister riding shotgun was fucking epic, and was more... cinematic than Halo 3's 'random things are exploding for no really explainable reason because they are all wafer thin' sequence.
Whoever said Crysis fails, lol - the end is one big 'to be continued...' - though I hear Warhead has a better one. Crysis falls into the trap that many games do of just throwing in a random boss fight at the end that has no real meaning for the player or build up in the game play. Quake 4, Crysis, Prey and Halo 2 are all culprits that throw themselves out for mentions - Zelda is an example where practically the entire game is based around getting to that final boss fight, and makes it clear throughout just how bad Ganon is, so when you finally meet him it feels so good to royally kick his ass >
