As someone who doesn't care about story and cutscenes, I can't think of any. The only thing that comes to mind is Unreal where the camera flys around a castle. That's actually before you even open the menu to start a game though.

Also Total Annihilation intro makes me want to scrap some metal.

Nice summary of gameplot as well ;)

I would count the whole beginning - leaving the ship until you breathe fresh air (or not, because of that "breathtaking" view of the waterfall) as the opening. And it was pretty impressive at the time.

Omikron
https://www.youtube.com/watch?v=NiGrClPf7X8

I'm a big fan of the Ys 2 opening. It probably doesn't make a shred of sense if you haven't played and beaten the first game, but at least the music's cool either way.

The new Wasteland 2 opening is pretty neat too.

I'm not a great fan of cutscenes in games, but a good (and brief) opening movie can really set the mood.

When it came out back in the Dawn of Time, the opening to Half-Life was one of the most amazing things I had ever seen. And it wasn't just me: a couple of friends of mine used to invite people over to watch it.

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

This isn't much of a mindblowing intro but it was a childhood classic of mine: https://www.youtube.com/watch?v=auQv90TfXrI

Dungeon keeper:
http://www.youtube.com/watch?v=8N5ODEGR1KQ

Might and magic 7:
http://www.youtube.com/watch?v=hQwVlY2YxzU

Ultima 7:
http://www.youtube.com/watch?v=JfqOZlNxbfI

Dunno if anyone mentioned it yet or not but <span class="bold">Medal of Honor: Allied Assault</span> has a pretty cool and intense opening sequence including the starting gameplay.

ah yes.in 1998 it indeed was a surreal experience.

The best game opening to me has great music like Civilization IV and Final Fantasy VI (SNES version) (Playstation version).

A couple I have always thought awesome:

Warcraft 3
Dark Reign
KKND 2
Heart of Darkness
and possibly my favourite:
Red Alert
or maybe this one:
Dawn of War!!!!

I'll take a slightly different tack to some of the other replies and suggest Sam & Max: https://www.youtube.com/watch?v=4yWnnk9fyJE.

I think it almost perfectly captured the tone of the game (not just the cut scenes) in a way that many openings fail to do. Also it made me laugh. Who could ask for anything more?

Sonic Adventure
https://www.youtube.com/watch?v=lO6Tc2xaAwE#t=32

Fur Fighters (Wish it was on GoG)
https://www.youtube.com/watch?v=jneWDIwXId8#t=54

Toy Commander
https://www.youtube.com/watch?v=D63MoR6wK2s

Little Big Adventure 2
https://www.youtube.com/watch?v=KljAkCs4YXU#t=23

Fallout OUR DEDICATED BOYS KEEP THE PEACE IN NEWLY ANNEXED CANADA.

Quake 2

Grim Fandango