Off the top of my head, there’s Love, Me by Collin Raye. Probably the most bittersweet song I’ve ever heard.
Oh yeah, and Seasons in the Sun by Me First and the Gimme Gimmes.
Some of my favorites are by Mecano. The lyrics are in Spanish, but if you know some Spanish I encourage you to check these songs out. The singer, Ana Torroja, has a beautiful voice and the lyrics are very poetic.
http://www.metrolyrics.com/mecano-lyrics.html
Specifically, check out
Aire: The protagonist dreams she is air and commits suicide by jumping/blowing out the window.
Cruz de Navajas: A love triangle ends badly.
Naturaleza Muerta: young beloved husband lost at sea.
I’m surprised noone has mentioned that old classic of Bobbi Gentry “The Tallahatchie Bridge.”
Cicero, I feel the same way you do about * Loch Lomond*.
One of my favorites is The Long Day Closes, by Arthur Sullivan. Here’s a wiki link to the lyrics. I didn’t know until I read the wiki article about the instrumental version used in Topsy-Turvy.
That’s actually a remake of Barry McGuire’s “Eve of Destruction”, from 1965. And (maybe Bishop Allen changed it) the original lyrics are “You tell me […] that you don’t believe, we’re on the eve of destruction.”
I’m not sure that’s really about death so much as, like, World War III/nuclear holocaust.