Three Dog Night’s “Chest Fever” starts off with an organ intro that moves on to set up the piece, and provide it’s backbone. From the album One.
Rick Wakeman, Al Cooper and Patrick Moraz all come to mind. Been drinking too much for specific examples.
<nitpick> Three Dog Night’s version is a cover of the original done by The Band. </nitpick>
FTR I knew the Three Dog Night version first, myself. Great organ riff!
Argent’s “Hold Your Head Up”–the album cut has a great organ solo by Rod Argent.
“I’m OK” by Styx (from their *Pieces of Eight * album) has Dennis DeYoung playing a cathedral pipe organ.
Light my Fire
Getting back to pipe organs, the songs “Parallels” and “Awaken” on Yes’ album Going For The One feature Rick Wakeman on church organ (recorded at St. Martin’s Church in Vevey, Switzerland).
One of their best 20-minute epics, “Close To The Edge”, also features an excellent organ piece in the “iii. I Get Up, I Get Down” section, also played by Rick Wakeman. Liner notes don’t say anything about a church on that album, though.
Party- Boston
Let It Be- Beatles (has a little bit of organ before the solo)
There are other Beatles songs with organ. The three that come to mind for me are by George Harrison: Only a Northern Song, Blue Jay Way and Long, Long, Long.
I’ll just note very geekily that a Hammond is not a pipe organ.
Let’s Go Crazy by Prince
Real pipe organ, not B3.
Come to think of it, Genesis featured Tony Banks’ organ pretty much throughout the early stuff, when Peter Gabriel was still the singer and one or two albums beyond that point. Organ players in rock seem to get dissed a lot; I’ve heard it in regard to Ray Manzarek as well as Tony Banks, and you don’t usually hear of an “awesome keyboardist”, the way you would a guitarist.
Maybe people think it’s too easy to play to be of any consequence, or maybe it’s the “accordian” factor–that of a loud keyboard instrument.
One of my lifetime top ten albums, Diamanda Galas and John Paul Jones’s The Sporting Life (her “pop” album), has a lot of fantastic wailing, wobbling Hammond organ on it. Find “Baby’s Insane” for one of the best Hammond tracks ever.
Ever.
Absolutey - the Hammond B3 with a Lesley cabiniet is one of the great sounds of blues-rock. For you youngsters reading, that was back in the day when musicians actually PLAYED their instruments and SANG to a real tune, rather than sample others’ music and “rap”.
The Murder City Devils used (non-pipe) organ fairly extensively through their unfortunately brief career.
Boom swagger swagger boom, baby. 
**Uriah Heep **had quite a bit of (not pipe) organ. The organ was probably just as much a lead instrument for them as the guitar.
The Buckinghams were big on the organ thing. (My dad played with a guy who eventually became a Buckingham. Brush with mediocraty! He ended up in jail, I think.)
Take Your Time by Buddy Holly. The organ was played by Norman Petty’s wife.
Well, since we seem to be doing organs in general and not just pipe organs, two of my favorite examples:
“The House of the Rising Sun” by the Animals has a crazy organ part, including a solo, and
“96 Tears” by ? and the Mysterians has a nice minimalist organ part.
The Rolling Stones - “You Can’t Always Get What You Want”
Spencer Davis - “Gimme Some Lovin’”
The Crazy World of Arthur Brown not only used a Hammond organ as the lead instrument, it lacked a guitar entirely.