Distinct results from view - selecting the first record

Question

I am searching parents according to child properties
I have this view returning parent and child information, I cannot change the view definition because I need to provide the child info for filtering. Since different children can be related to the same parents, I don't get distinct results
How can I force SQL to return single parent results by using the first child found?

For example:

parent1     foo1        child1      data a    
parent1     foo1        child3      data a    
parent2     foo2        child1      data a    
parent2     foo2        child3      data a    
parent2     foo2        child4      data a

What I would be happy to get is:

parent1     foo1        child1      data a    
parent2     foo2        child1      data a

Sample scripts:

drop table relations;
CREATE TABLE relations(
    parent_id nchar(10) NULL,
    child_id  nchar(10) NULL
) 
drop table items;
CREATE TABLE items(
    item_id nchar(10) NULL,
    data nchar(10) NULL
);

insert into items values('parent1', 'data x');
insert into items values('parent2', 'data y');

insert into items values('child1', 'data a');
insert into items values('child2', 'data b');
insert into items values('child3', 'data a');
insert into items values('child4', 'data a');

insert into relations values('parent1', 'child1');
insert into relations values('parent1', 'child3');

insert into relations values('parent2', 'child1');
insert into relations values('parent2', 'child2');
insert into relations values('parent2', 'child3');
insert into relations values('parent2', 'child4');

drop view v_parent_child;

create  view v_parent_child as
    select rel.parent_id,parent.data as parent_data ,rel.child_id, child.data as child_data 
    from relations as rel
        join items as parent  on  rel.parent_id =  parent.item_id
        join items as child on rel.child_id = child.item_id
;


select * from v_parent_child where child_data = 'data a';

score 1 · Answer 1 · answered Jan 14 '16 at 00:56

Try the below view.

create  view v_parent_child as
    select rel.parent_id,parent.data as parent_data ,rel.child_id, child.data as child_data 
    from ( select parent_id, 
    child_id
 from
( 
select parent_id, 
    child_id,
    row_number() over (partition by parent_id order by child_id) rn 
from relations  ) oneChild
where rn = 1 )  rel
        join items  parent  on  rel.parent_id =  parent.item_id
        join items  child on rel.child_id = child.item_id

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

you can do row over partition in the join.

select parent_id, parent_data , child_id, child_data  from (
        select *, 
        row_number() over (partition by parent_id order by parent_id) as rn
        FROM v_parent_child) t
        where t.rn = 1 ;

like here: Oracle 'Partition By' and 'Row_Number' keyword

edited May 23 '17 at 12:31

Community

1
1

answered Jan 14 '16 at 00:58

jae555

140
2
7

Bingo, thank you! since the cost is lower from the other query posted i will accept – JavaSheriff Jan 14 '16 at 14:02

Distinct results from view - selecting the first record

2 Answers2