Home > Software engineering >  Add information to one table from table contains duplicates
Add information to one table from table contains duplicates

Time:11-19

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.

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;
  • Related