The London production of
La Cage aux Folles with Roger Allam and Philip Quast: they sit on the steps singing the final reprise of "Song on the Sand", after which they kiss as the curtain falls - simple but effective!
Lots of others.
Hairspray's is unbeatable for pure fun and excitement, it's always had a well-deserved standing ovation whenever I've seen it, as soon as the song ends (occasionally before!).
But sometimes the opposite is even more satisfying: sometimes the audience are just stunned into silence so there's quite a long pause before they start to applaud - it takes a while to sink in. The London production of
A View from the Bridge with Ken Stott did exactly that, as did the McKellen
Waiting for Godot. I think it's more common in plays than musicals, but if they're done right,
West Side Story and
Cabaret (just to name two) can also have that effect.