Beyond being the Federation flagship and one of the best ships in the fleet, they're the ones who get all the important mission. With the Aries, his mission was going to be months even getting there in what Picard termed an obscure mission. They were mentioned having docked at DS9 in their season 2 so they were back at least 5 years later. They were also mentioned in Nemesis as being part of a fleet deployed on the Romulan border. So... obscure exploration missions and routine stuff inside Federation space for the most part.
Riker turning down command of the Melbourne was cryptic in a way being it was later destroyed at Wolf 359. Sort of an eerie choice that he could've been there... wondering if he'd have fallen too or if he could've made a difference. Still it was an old Excelsior class. Still that it's an older ship that was mentioned in 11001101 as being available to chase the Enterprise, and is later close enough to mount a defense at Wolf 359 suggests it's kept in the core territories of the Federation on routine duties. That might not be what Riker wanted.