To rank the Silent Hills on scaryness, from most to least: - SH 3 - SH 1 - SH 2 - SH 4 - all the others
Everything else, no particular order: - the 4 Fatal Frame games - Amnesia - the 3 Forbidden Siren games - Eternal Darkness
Resident Evil was never scary. All it ever did was suddenly make loud noises in your ear. That is not horror. They were fun to play, but never had a legitimate fear factor. If RE is considered "scary", then House of the Dead must be terrifying.
I'm curious, what's Fatal Frame like? I read it was a bit like Silent Hill?
Fatal Frame (Project Zero) is not really like SH in my opinion, but that's not a bad thing, they are both atmospheric and leave you feeling alone, but in very different ways.
I would recommend Project Zero 1 above 2, I thought the story was better and the lighting effects definitely are better (which adds to the atmosphere, I'm not a graphics snob). 2 has better combat, but that is secondary to the overall experience for me and the combat in 1 is in no way bad, they just improved it with the sequel.
Also, how the hell could I forget (and how no one has mentioned) The Thing? That game really captured paranoia in game form, the way your team mates freak out and don't trust each other then randomly one will "Thing out" and you have to take him down without getting everybody killed. I never actually completed it, but the large chunk I played was awesome.