One of the most impressive examples is an 11-minute medley of video game and cartoon themes: http://www.youtube.com/watch?v=Xz0PaPpmGa8
There are some good examples already (the GTA4 negative wheel friction hack (http://www.youtube.com/watch?v=I0cUkU9rZyE) or some of the Mario64 Tool-Asisted-Speedruns come to mind), but we're not yet anywhere near these beautifully orchestrated sublimations of classic Mario, Zelda and Super Metriod.
But perhaps you're right. There does seem to be a sweet spot in the balance of 16-bit era games, where the technical complexity wasn't so constrained as to force a boring outcome (as with 8-bit and earlier), nor so complex as to require a herculean amount of effort.
Time will tell. I would rather err on the side of being hopeful.
Not really a mod - but certainly has the love/devotion thing down.
Then, Halo 2 came out with off-the-shelf Havok physics replacing the (wonderfully) "buggy" penalty springs-based physics from H:CE. Because Havok was "more-realistic", it was extremely difficult to do the sort of insane physics-stunting the first game allowed. It was this single change that turned me off of the rest of the Halo series.
Like Super Metroid, H:CE was a magnificent case of the weaknesses in code allowing for brilliant system-breaking ingenuity on the part of users. And as long as video game devs are forcing themselves to the very bleeding edge of technology under a merciless deadline, we're bound to see more "glitchy" games offering plenty of opportunity to enterprising and creative sequence-breakers, speedrunners, stunters, and the like.
Somebody took a copy of Super Mario World for the Super Nintendo and hex-edited the levels so that instead of requiring a human player, the game's built-in physics propels Mario through the level. And, the levels were designed so that the various sound effects caused by Mario stomping on and bumping into things vaguely approximate the music to Don't Stop Me Now. AND four entirely different levels were created, each attempting to reproduce the part of some particular member of Queen. AND they occasionally sync up for the more spectactular parts of the song.