How should I filter for distinct values on the union of multiple columns rather than the intersection?
I'm looking to filter data like the following...
| col1 | col2 |
|---|---|
| a | 1 |
| a | 2 |
| b | 1 |
| b | 2 |
...into a result set like this:
| col1 | col2 |
|---|---|
| a | 1 |
| b | 2 |
(generically - no assuming that real data will be neatly sequential like this)
Standard DISTINCT behavior on multiple columns returns rows with a distinct combination of values, which would return all rows in the original set because no rows have the same value of col1 and col2 simultaneously.
Nested DISTINCT filters on each column yields only the first row unless the data can be cleverly sorted.
Instead I'm looking for a filter that yields rows that are unique on all of the chosen columns individually - i.e. a "union" distinct operation rather than an "intersection" distinct operation. Is there an idiomatic or at least scalable way to do this?