I have the following table:
In Table_1, (ID, Name) pairs can repeat and have any combination
Table_1:
| ID | Name | Value1 | Value2 |
|---|---|---|---|
| 1 | John | 34 | 45 |
| 1 | John | 15 | 78 |
| 2 | Randy | 67 | 12 |
| 2 | Randy | 40 | 46 |
| 1 | Randy | 23 | 85 |
| 2 | Holmes | 10 | 100 |
I want to find all information for all unique pairs. So the output should be:
| ID | Name | Value1 | Value2 |
|---|---|---|---|
| 1 | John | 34 | 45 |
| 2 | Randy | 67 | 12 |
| 1 | Randy | 23 | 85 |
| 2 | Holmes | 10 | 100 |
When I do SELECT DISTINCT(ID, Name) I get the unique pairs correctly. But how do I add value1, value2 columns to this. Because adding value1, value2 causes the pairs to repeat.
>Solution :
You may use DISTINCT ON here:
SELECT DISTINCT ON (ID, Name) *
FROM yourTable
ORDER BY ID, Name;
Demo
This will arbitrarily return one record from each (ID, Name) combination. Note that if you wanted to choose which of the duplicate pair (or more) records gets retained, you could add another level to the ORDER BY clause. For example, to choose the duplicate record with the highest Value2 value, you could use:
SELECT DISTINCT ON (ID, Name) *
FROM yourTable
ORDER BY ID, Name, Value2 DESC;