Well, here are my top picks. Keep in mind that although I own all of the Silent Hill games, I have not yet played a significant enough portion of them to truly rank them on the list.
5) Resident Evil: Code Veronica (horror style: zombie) - I seem to be the only person I know who champions this game as the pinnacle of the Resident Evil series. Sure, RE4 did something different, thus breathing new life into the series, and I respect and applaud that. It was a good game, for sure. But there was something a lot creepier to me about Code Veronica. RE4 was more "Oh shit! Something jumped at me! Oh shit! The town is chasing me and I'm running out of bullets!" kind of scary, but Code Veronica, more than any other game in the series was the kind of scary where I found myself thinking "I really don't want to be here." This is the kind of scary where it's almost worse when there are no enemies on the screen, because the anticipation kills you. At least when the zombies attack, you know where they are.
4) Clocktower (horror style: slasher) - The scariest game ever, in terms of outright panic-inducing. Hearing the scissor man approaching from the distance and getting closer and closer always makes my heart stop.
3) Fatal Frame (horror style: J-horror) - A perfect blend of eeriness, jump-out-of-the-seat terror, and unsettling creepiness, Fatal Frame takes all the right pages out of the movies and truly absorbs you within the experience so that you always want to know what happens next, but are too scared to find out. Awesome.
2) Alone in the Dark (horror style: Lovecraftian) - Forget what you know about the movie and any of the sequels. Place yourself back in a time when polygon animations were brand new. By this point, you're also entirely unfamiliar with fixed camera angles that changes perspective with each section you walk through. Now imagine that you're in a haunted mansion where every door can possibly have a monster waiting behind it. Also, you don't know what weapons might or might not be able to kill certain monsters. Also, you don't know if the monster can even be killed at all. Also, you don't always even know what might be a monster. See that suit of armour? DON'T TOUCH IT! See that ghost sitting in the chair? Be VERY CAREFUL around it! See that painting on the wall at the far end of the hallway? It wants you dead! You never know if and when you're safe, or what could possibly kill you. If you haven't played the original Alone in the Dark, I might even be so bold as to say you have not yet experienced true video game horror.
1) The Colonel's Bequest (horror style: murder mystery) - This game has no monsters. This game has very few threats. You can count on one hand, how many chances there are in the game that you will be killed by the actual murderer. Dying is usually caused by random mistakes (should have fed that horse a carrot before trying to get into its stable) or sheer stupidity (shouldn't have walked off the dock). This game, however, can terrify you with its atmosphere alone. Even scarier is when you play it on an old, slow computer with black-and-amber monochrome video. Heavily borrowing from Agatha Christie's "And Then There Were None," The Colonel's Bequest found you as Laura Bow, a journalism student who is invited to an island plantation by a friend, to spend the weekend. Along with her uncle, an old Colonel, and his relatives and trustees (accountant, doctor, etc), it is revealed that the Colonel has invited everybody (except you) to the plantation so he can announce his will: everybody will get an even share of his money upon his passing. However, should anybody on his will pass away before him, their share will be divided evenly amongst the survivors.
Naturally, people turn up murdered, one by one. You have to spy on many conversations to figure out everybody's relationships to each other, possible motives, and of course, process of elimination, in order to determine who the murderer is. There was something very eerie in this game, far beyond any actual threat to my character's well being. Something was scary just in KNOWING that there WAS a murderer, and that they were moving around. Things happen as time passes in the game, whether or not you're there to see it, and for all you know, the murderer might be someone in the same room you're in right now.
I'm not sure exactly how dated it is now, for anybody who hasn't played it back in its day, but I really can't recommend this game enough. It is incredibly elaborate in its storytelling, in such a way that as much as I've already blabbed on about it, there's still so much more to say. Maybe I'll review it one day.