I agree with Choupolo, Final Fantasy always had long but interesting intros and ending scenes:P
I also could say that GTA-SA also had an odd ending. Here you are at the end of the game, you can wield every gun,grenade,bazooka the whole game is about gangster life and at the end of it all Carl says "well see you I'm going out" or something like that.
I also liked the Dawn of war intro
oh i nearly forgot the starcraft intro... or was it the extro?? anyway one marine runs up to another and asks him where is the air support
the other marine with a cigaro in his lips simply points up in the sky at a giant mothership
