Final Fantasy 13-2 is probably one of my favorite intro sequences in recent memory. It's just too bad you don't use Lightning for the rest of the game. But it was cool in how it introduced some of the new gameplay stuff right from the get go, and it made the game look more promising from the start. It's kinda what got me to think that it wouldn't be as bad as I thought. And I was right. I also watched the intro a lot.
Also, as far as just plain intro songs, my favorite is still Tales of Vesperia. Hated it at first, but it grew on me. Still never beat it, feel like I'm close to it though
Worst ones (as far as intro sequences) are probably Kingdom Hearts 2 and Breath of Fire IV. Oh man, I love em both, but I hate starting em over.