Wow, you guys went old school. There are so many great ones that I could spend way too much time posting them, but here are a few more modern ones.
On the hand written tip a la Dr. Strangelove is Almost Famous
Apparently that's Camron Crowe's hand doing the writing.
I looked it up and they (Fincher I guess) had the books in the movie / opening sequence actually written.
I've said it before but I'll say it again - the Alien Trailer is the best ever and the the opening credits copy it.
As for old school and sex you can't beat Barbarella (NSFW)
I love creative opening movie credits and I think most people who are a bit geeky about them will agree that Saul Bass is the king of them.
Here are his opening credits for North by Northwest.
Anyone have others they particularly enjoy?
edit: forgot to mention Art of the Title an entire website dedicated to opening title sequences. Enjoy.