1. Starcraft--especially the Dragoons uncloaking in the Terran encampment. The Aliens rip off was cool too.
2. FF7--the final scene, coupled with the excellent music was really top notch.
3. D2--wish they spent as much time allocating server space and working on bugs as they did on the cinemas
4. Dungeon Keeper 2--short and sweet and not too serious
5. TombRaider series--except for the first, the games sucked. The cinemas were the only good reason to keep playing.
6. ResidentEvil--for one cinema alone--the first zombie that turns towards you. Scary.
If intros count, AoE2 and MW3 as mentioned. That's all I can think of with the ones I've played. I'm sure Warcraft3 will conquer all.