I don't think anything will ever be as awesome as Ganondorf/Ganon was in OoT. It blew my little mind at the time, and it's still a lot of fun.
Now that people are mentioning it though, the last boss from Paper Mario: Thousand Year Door has to be pretty up there, too. I was just thinking about it the other day. It's just a silly Mario game made up of paper-looking sprites, but that last battle gives me chills just thinking about it!
Currently playing:
Bloodbath Paddy Wagon Ultra 9







