These absolutes are difficult to assess -- so many years, so many films. But arguably the most memorable, audacious main title sequence over the last decade is from "Birth." In direction, photography, and music, Jonathan Glazer, the late Harris Savides, and Alexandre Desplat take that prologue and completely knock it out of the park.
Consider the music AND the presentation when you are deciding.
In his recent (2013) BBC TV series The Sound of Cinema Neil Brand starts his exploration of film soundtrack music with my favourite, showing how the music integrates with the visuals and draws the viewer in ...
The Ipcress File ... perfection!
Many other good/great titles mentioned but having perfect music aids choosing.
I'm a big fan of the opening sequence from Brian de Palma's The Untouchables. Also love The Empire Strikes Back, but that's more because of Malachi Throne's narration on the "story of" LP from the 80s than any differences with any of the rest of the film openings in the series.
I think Tim Burton is great for, with a few exceptions, continuing the tradition of the main title sequence in recent years when it has fallen more and more out of style. Of his movies, I think all of them are great, but "Edward Scissorhands," "Mars Attacks!" and "Sweeney Todd" are the best, since in addition to the great music they visually capture what will eventually be the spirit of the entire movie.
mmm....... How about 633 SQUADRON? The simple idea of filming cloud formations from the rear of an aircraft is instantly transformed into an exciting visual tour de force thanks to Ron Goodwin's rousing music.
Whenever I need a "balls to the wall" kind of opening I'll either chose Howard's SIGNS or Velasquez's DEVIL...both main titles grab you by the short and curlies and shake you til you're quivering. I love that each have a completely different visual for the titles but one is no less effective than the other.
Like many, my first thought was Superman The Movie.
But if we say that movie title sequences can incorporate four great elements: score, visuals, titles themselves and performance, the latter removes a lot of obvious choices from the list.
The only example for me that ticks all the boxes is the opening sequence for Lolita (1997). Spectacular visual landscapes, great Morricone score, amazing titles as a wasted Jeremy Irons swerves his car from one side of the road to the other 'wiping out' the credits as they appear on the screen, and of course his performance and voice over: 'She was Lo, plain Lo in the morning...'