Good thread idea. Chrono Trigger and FFVI will always be favorites of mine, but those have already been mentioned. I love intros that get me excited to play the game; for non-RPG/adventure, here are two more recent examples that did so (though the Youtube versions really remove a lot of the impact):
Zone of the Enders: The Second Runner (you'll have to excuse the sub-standard voice acting)
Jet Set Radio Future (you can skip the first 45 seconds or so)
And for RPGs, the initial section of Shin Megami Tensei: Digital Devil Saga contains more awesome than can be reliably calculated (though again, view the real version for full effect)
Tagged: GooseGaws - <--- Has better taste in games than you.