For usefulness, the elemental tongues let you give detailed commands to summoned elementals, as does Sylvan for fey, while Celestial, Abyssal and Infernal see less use because most speakers of the three can speak any damned tongue they please anyway.
Other than that, Draconic, Elven and Dwarven show up disproportionately much in modules, so they're handy to know. The Order/Chaos languages are rare enough that you could use them as a secret party language, but other than that I've never seen them put to use.