Chrono Trigger and Final Fantasy 6 come to mind. Final Fantasy was already suggested above, even! But yeah, while I could state that there's a huge amount of nostalgia involved, it doesn't erase the fact those are some of the most popular and liked JRPGs released in the history of ever.
On the side of Western RPGs, I would also suggest two lesser known titles: Shadowrun. To be more precise, both Shadowrun titles the SNES and the Sega Genesis (which are wildly different). Both do a great job in building and showing the Cyberpunk feautures of the world and contain great stories. I guess it could be debated which one has the bigger spoke (the SNES game being more linear, story-wise, while the Sega Genesis version was sandbox -and one of the first one on consoles at that- meaning you technically do your own story as you go along).
The last one I would suggest would be Metal Gear, but ONLY the first three before Kojima went full Kojima with the later titles and it became kind of a crazy mess. They're still good games, mind you. But if you want to stick with the ones that ACTUALLY make sense, those would be Metal Gear, Metal Gear 2: Solid Snake, and Metal Gear Solid.
Also, while the genre is a bit... controversial, if you're willing to count visual novels as games (some people don't), Kojima's own Snatcher is also VERY heavy on the story (since that's basically its main selling point). The world building is GREAT, as are its first two chapters... which is good since it balances the story which, while huge on scope and espionage, kind of falls apart in the last one.
Oh, and of course, Silent Hill 2. I shouldn't need to explain that one.