I have the following table:

In Table_1, (ID, Name) pairs can repeat and have any combination
Table_1:

I want to find all information for all unique pairs. So the output should be:

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.

CodePudding user response：

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;

CodePudding user response：

try row_number and partition by.

SELECT * 

FROM   (
select *,
row_number() over(partition by Name order by Name desc) rn 
from Table_1) as a

where rn = 1;