I have to acknowledge how incomplete this list is from the start. I couldn't make this a "top" list because most movies have opening titles, so right off the bat I am forgetting dozens, nay, hundreds of excellent candidates. It's not like making a list of say, dinner table scenes. So rather than try to make a definitive list, these are just examples that stuck out in my mind and are worth checking out.
1. Napoleon Dynamite. I don't care what you ultimately thought of this movie. When you saw the credits spelled out endearingly on or with lunches, school supplies and chapstick to the sweet sounds of "We are Gonna Be Friends" by the White Stripes, you thought it was pretty awesome.
2. The Graduate. A good title sequence sets the tone of the film, and The Graduate is an amazing example. Simon and Garfunkel accompany Dustin Hoffman's seemingly endless journey down the moving sidewalk in the airport... into his future!
3. Catch Me If You Can. The movie is basically acted out here in the span of a few minutes by little retro animated figures. Fun!
4. Amelie. This whole film is pretty dreamlike, and the titles set that up by showing a young Amelie playing and making silly faces with some surreal effects. Really beautiful. (Titles are a little bit into the clip.)
5. Sweet Charity. This is one of my favorite "day in the life" type of sequences. It shows Charity skipping around New York City, going about her morning in her cheerful Shirley MacLaine fashion, occasionally using a colorized freeze frame to freeze her in a goofy pose and slap some credits on. Really captures the essence of the character. (Clip not available - boo.)
6. A Hard Day's Night. I included this as more of a fan favorite, because if you pay attention, you'll notice that the actual credits here are pretty sparse. It's more of just an opening sequence, but oh well. Endlessly imitated and parodied, there is still a sense of silly fun in the John, Paul, George and Ringo's attempts to escape their maniacal female fan base. See also the opening credits of the first Austin Powers, which is a loving homage to this. Speaking of Austin Powers...
7. Austin Powers 2: The Spy Who Shagged Me. Austin frolicks about naked, while words or objects obscure his junk. Then there's a synchronized swimming sequence. Two things: first, this means that people actually sat around and thought of ways to obscure Mike Myers' junk, and second, I feel that as a cast or crew member, it would be an honor to have my name obscuring Mike Myer's junk.
8. The Women. This bitchy classic even has bitchy credits: each elegant star is introduced with her name accompanied by a clip of her smiling, and then an image of the animal she most resembles. The cunning one is a fox, the fat one is a cow. Ouch! (Clip not available - perfect excuse to watch the whole fabulous flick!)
9. Spider-Man 2. In the first Spider-Man, the credits were spelled out into a spiderweb, accompanied by Danny Elfman's kickass score. The second film's opening keeps the web motif, but cleverly adds clips to sort of recap what happened in the first one, and also mixes in comic-style illustrations. Engaging, energetic, and gets you totally pumped.
What are your favorites?