Well I was always a fan of the "Link to the Past" on the ol SNES.
As for video game of all time, the only game that had me sucked into it for years, even after I beat it, was Half-Life and it's mod collection Team Fortress Classic, Day of Defeat and Counterstrike...... well actually I hated Counterstrike, but the for the price of one game, getting 4+ other games for free...... a game that's been around for a decade now, and still has thousands of servers to join onto today..... that to me is pretty impressive.
But since I'm an artist, animator, etc... I also play plenty of games, so picking one overall amongst the loads I've already played..... from Atari, Commadore 64, Coleco Vision, Vic 20, Nes, to today..... I gues Half-Life had the most impression for me as I have never had a game yet besides that one that I would still have a lot of interest in playing.... even after having it for 10 years.
All the other games, such as Zelda and the sort I still like, but I still need long breaks in between to foget things before I can enjoy playing them again..... if not, then it's all matrix routine, where I know every corner of the game, do everything systematically and it's beaten in a few minutes.
Ocarina of Time was one I never got into believe it or not..... and I played just about all of them, including the new one, The Twilight Princess (that was pretty good)
Although I do have it for my Gamecube..... perhaps someday I'll get into it..... but I usually get as far as the first dungeon after getting my sword and sheild and then I turn it off never to get back to it again.