The opening to Half Life one was pretty good, especially when you go back to it and see the little bits that are relevant to the later game/expansion packs.
I think you'd have to include Super Mario 64 for being the first truly 3D console game, there would have been a far greater anticipation after watching that than any game since.
Contemporary choices would include the two Mass Effect games.
As I'm in an Amiga mood right now, and also feeling for David Jones and the total feckup he made with his company putting Dundee back into the third world in the process, I'm saying feck it!