Out of the more recent ones, this one comes to mind:
Admittedly, it's not made of in-game footage, however what makes it awesome is that everything that's in this trailer can be seen and done in the game.
Now, this one:
Shows a completely different look and style and yet has so many nostalgic moments, like the game itself. A big plus is that this one is made entirely of in-game footage.
This one's a bit older and lacks the presently popular Hollywood blockbuster look, but it's still among my favorites:
Shows a lot of gameplay, just enough of the story and, again, entirely made of in-game footage.
Other than these, I highly recommend the "Hope" trailer for The Witcher 2:
and pretty much any L.A. Noire trailer. At least that's what I can think of at the moment.
