Bowie’s “Space Oddity” was pretty unique:
My pick for now is Pete Wingfield’s “Eighteen With A Bullet”, sung in falsetto.
I’m eighteen with a bullet
Got my finger on the trigger, I’m gonna pull it
I’m picked to click now
I’m a son-of-a-gun
So hold it right there little girl, little girl
We’re gonna have big fun
I may be an oldie, but I’m a goodie, too
I’ll last forever and I’ll be good to you, oh, yes I will
Of course, the song actually did wind up charting at #18 with a bullet, eventually reaching #15 (#7 in the U.K.).
Since Chuck Berry had a total of seven top ten hits and lots of airplay with other songs, I don’t think it’s fair to say he “only hit it big” with “My Ding-a-Ling”.
I have a soft spot for minimalist songs, but rarely one gets in the charts. So I thought of which did, and I came up with these:
The aforementioned “Popcorn”:
M’s “Pop Muzik”:
And of course the mother of all minimalist pop songs, Trio’s “Da Da Da”:
Banana Boat (Day O) by Harry Belafonte, which made it high on the charts. Basically a song about a crew loading bananas.
Turning Japanese** by The Vapors is pretty strange, regardless of whether it’s about self gratification or not
“Walk the Dinosaur” by Was (Not Was) was the first time I recognized a novelty song for what it was. It hit #7.
And speaking of novelty dance songs about walking, “Walk Like an Egyptian” by The Bangles is pretty weird when you actually listen to the lyrics. It stayed at #1 for four weeks.
Well, it’s not really a rock…
But you’re right.
How about “Coconut”?
^^
Yeah, that’s a good one too. Most of my life, I had only known it as a Muppets number before I learned that originally it was a Nilsson song.
I immediately thought of “Spill the Wine,” by Eric Burdon and War. A four-minute stoner/horndog rap set to a killer groove. Made it to #3 on the Hot 100.
I remember it. It was played on a lot of the classic rock stations when I was growing up, although it’s been more missing than present in the last couple decades.
Og, I’m old.
I always wondered how the dinosaur felt when the chorus shifted gears and started singing, “Open the door get on the floor, everyone kill the dinosaur.” Like, was the dinosaur thinking, “What the hell, guys. I thought we were cool?”
I used to have the 45 single of that!
As did I. 
Given my user name here, it shouldn’t surprise anyone that, these days, I have that song (as well as the full, 15-minute album cut) on my iPhone.
What about Devo’s Whip It?
Reached 14 on the US Billboard Hot 100. The video was also a mainstay on early MTV.
It is indeed unusual. I remember reading somewhere that is somewhat unique in its structure. Same few notes, no ‘chorus’, no ‘bridge’ (I’m not really a music guy, so you may want to look it up) and some other things about it that made it special.
“So let me get this straight…”
Jungle Fever, a 1971 4’20" orgasm. Number 8 on the charts.
Like one commenter said, after listening to that I need a cigarette, and I don’t smoke!
(It was one of the samples in Convention 72.)
And its cousin, Donna Summer’s Love to Love You Baby, another mutliorgasmic 1975 hit (#2)
The two songs that popped in my head, I haven’t found searching this thread.
AC/DC, Big Balls:
Presidents of the United States of America, Peaches:
Though I admit, with Peaches the song isn’t quite as weird as the video.
I’m not counting songs by novelty bands like Bowling for Soup or Green Jellÿ.
ETA: Would Wynona’s Big Brown Beaver by Primus count? Primus is pretty weird in general, but this song in particular stands out.
As long as we’re hating on spoken word songs, let me put in a word for Desiderata. Peace, love, harmony and all that droned through by a deejay/failed talk show host. #8 on the Billboard Hot 100.
No Kiddin’, Huh? I clicked on that and felt a ripple through The Force!