The game is Chrono Trigger. I'd somehow managed to miss playing it until a month or so ago. The moment itself is a fairly late game spoiler, so proceed with caution if you haven't played it. Then again, if you haven't played it, and you have even a passing interest in RPGs, then you're being as silly as I was - go play it.
I'll wait up.
Shit starts getting real when you reach the floating kingdom. It initially seems to be an idyllic place where magic users make the world a better place (for magic users, that is... non-magic users are relegated to spice mining, or whatever it is that people who can't conjure fire from thin air do). There's a constant air of dread, though. It starts subtly, with various townsfolk noting that the queen doesn't seem herself lately, and a few people noting that they've found a 'new' source for their magical powers.
Then a creepy kid walks up and basically says that someone is going to die.
|You know, you were a dick even when you were a little kid, Magus...|
...does absolutely nothing......except piss Lavos off.
Your party lines up to fight, and gets curb stomped*, the shadowy prophet shows up, and turns out to be Magus in disguise. He's waited for decades to come back and get his revenge on Lavos (a plot point which gets explained shortly after this scene), but he's unable to even put a scratch on the might abomination. Though the fight is hopeless, Chrono gets up and stands alone against Lavos, who kills him almost instantly. Knowing that there's just no way to win the fight, the remaining heroes are whisked away before the floating kingdom crashes to earth, setting the player up for the endgame.
* of course, if you're a high enough level, you can beat that part, but that's not how it went for me
The whole scene is done very well. The cinematic quality to it is extremely well done for the old SNES, and it was genuinely surprising to have the lead character sacrificed in such a manner (he gets better, of course). A tense and fulfilling setup scene.
If you want to relive the whole thing, I've embedded the video below.