franko_camron franko_camron - 5 months ago 11
SQL Question

How can I retrieve unique data from a table with father and son relationship on sql server

I'm have a table with father and son relationship, both, father and son are primary key, let me show you an example of the data :

Father | Son
A | 1
A | 2
B | 1
C | 1
D | 2
E | 3

So what I want is group by son, and get 1 of the fathers, doesn't matter which one, I know it sounds weird, but I only need to know one of the fathers as a reference, so I'm looking for a result like this:

Son | Father
1 | A
2 | A
3 | E

I tried to do something like this, in my query [code] is the son

select i.father, q1.code from
(select i1.code from itt1 i1 where isnumeric(substring(i1.father, 0, 3)) =1 group by i1.code) q1
left join itt1 i on q1.code = i.code where i.code is null

  1. I retrieve and group all the sons and encapsulated the result in q1 sub query

  2. Then I left join q1 with the table, but I'm not getting the result I want

Is it possible to do what I want?


You can use an aggregate function with the group by. For example:

select son, min(father) AS Father from itt1 group by son