Emily Oberman and Bonnie Siegler, in Saturday’s New York Times, sing the praises of great title sequences (as they point out, there’s no Oscar given for these). They give their picks for best 2008 title sequences, as well as a few from classics including The Palm Beach Story, Psycho, and To Kill a Mockingbird. Also mentioned in the article is the title sequence from probably my favorite movie ever, Dr. Strangelove. The title sequence is as gorgeously photographed, funny, and terrifying as the rest of the film.
For more great title sequences, check out The Art of the Title Sequence site—there are many stellar examples to be found there, including the one for Kubrick’s Lolita, (which was the film he made prior to Strangelove), and this winner from Delicatessen.