While it wasn't a great game... I always loved the intro to Interstate '76 (yeah, I own it)

http://www.youtube.com/watch?v=3JCGZFQZkdc

 

But Mechwarrior 2 was probably the best

Haha, I always loved that one, had completely forgotten about it!  Love the retro-style.

I think another great Intro that's technically not an Intro, but really uis, are Half-Life and Half-Life 2's openings.  They're playable, however you can't really interact as much as simply move forward in an a kind of interactive movie, complete with opening credits.  The atmosphere they make is absolutely perfect and a true testimate to Valve's level of quality.  Fantastic stuff.

i have to say i loved sins intro, but it still slightly pissed me off, i wanted a campaign to go with story of the intro

There is many great intros... So I will put just one, which I really liked and is only few weeks old

Recommend 1080p

http://www.youtube.com/watch?v=Ms95NKFnhCU

So I decided to play through Oblivion again, and I started up a new game and watched the intro.

 

http://www.youtube.com/watch?v=nGyw_0IG5s0

 

As it sweeps in on The Imperial City and the credits roll and the upbeat, military music plays, I get all teary eyed and nostalgic, because I remember that music from so many hour of Morrowind. Oblivion may be a poor pretender to the Elder Scrolls throne, but that intro always takes me back to better days.

 

Now excuse me, I need to go learn the french horn.

The two best I've seen are

the Might & Magic intro, which somebody mentioned on page 1

the Elemental Beta info.   I hope the "you travel north" intro and the little ditties like "...not such a mess....will make you cry less" will all find their way into the CD for the final build.