Your suggested example (below) doesn't work because you'd actually be reading from and writing to the same file simultaneously.
$ cat filename | sort | uniq > filename
The idea with a pipe or redirect is that the command on the left and right hand side of each pipe or redirect run simultaneously, in parallel. The command on the right processes information as it is handed over to it from the command on the left, while the command on the left is still running.
In order for your scenario to work, the command that reads from the file would need to finish before the command that writes to the file begins. In order for this to work you would need to redirect the output into a temporary location first, then once that's finished, send it from the temporary location back into the file.
A better way to do this is basically like in your former example, where you redirect to a temporary file then rename that file back to the original (except that you don't need to delete the file first, because moving deletes any existing target).
$ cat filename | sort | uniq > result
$ mv -f result filename
You could also save it into a string variable, except that only works when data is small enough to all fit in memory at once.