I kind of liked the intro for the first Ratchet and Clank game. It's easily the weakest installment in its series (that I've played), but the game had a great atmosphere and the intro set the tone for it nicely.
I can't really think of any other intros that have really grabbed me. If we're talking just the opening cutscene I'd say Wind Waker's is wonderful. But everything after that is pretty standard.