And I might be the only one that don't like Pets that much, even if I realy love animals in RL. I lost interest in horses in around 30 minutes, dogs started to be annoying after an hour of cleaning random veggies around the lot, and even if I am still liking my cat and small animals, I really don't like wild horses wondering around my mansion or ultra modern club.
I think that World Adventure is the best EP, as Sportsfan said. There are so many tombs in all 3 worlds that it take some time to complete them all and it is worth playing them several times. Plus 3 worlds to explore and awesome skills and collectables.
Ambitions - Professions added to diversity to the gameplay because you never know what will happen or where, and you're involved all the time. Definitely worth getting it.
Late Night - third best in my opinion - many new venues even if very similar to each other and with same function (clubs, bars and lounges - in all of them you drink, dance, play instruments and trow parties/socialise), Bridgeport and living in the City makes all the difference. I don't like vampires and celebrities, so it is just a new town and many entertaining objects for me (and skills I like).
Pets before Generations - Generations are worth getting only if you like playing legacies and family oriented Sims. Additions are minor in my opinion, plus no new town.
As for SP - if you don't like building it's a waste of money. I like OLSP the most, but as I said, if you don't like building - don't buy any of them.