I've seen dozens, if not hundreds, of E3 demos in my life, but for me, one of the most impressive is still the Half-Life 2 E3 demo from 2003. It revealed enough about the story to get you interested, and while it was still some 16 months away thanks to the hack/leak of the source code, damn if it wasn't jaw-dropping at the time.
The Xbox 360 was still two and a half years away when this demo was given, and what was shown was far, far ahead of even the next-gen launch titles, in my opinion. (Oh my god are we going to have deal with the term next-gen for the next three years now, again?)
Anyone have any other demos that they remember as being specifically amazing?