SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Aggregate data with nulls and duplicate values (chpt.2)

Carl COct 4 2018 — edited Oct 4 2018

Hi,

I've this issue and I hope you can help.

Thanks to Mathguy and Paulzip, I could solve fisrt issue with this dataset.

Nevertheless, after adding a new column "Town" into my selection I got a wrong result again with duplicate values

ID	Name	Surname	Birthday	town	My comments
1000	AAA	BBB	19000101	Frankfurt
1000	AAA	BBB	Null	null	Diff brthday
1000	AAA	BBB	19201006	London
2000	HHH	GGG	null	Null	Birthday Not available
3000	MMM	RRR	null	null
3000	MMM	RRR	19620723	null
4000	TTT	JJJ	null	Delhi
4000	TTT	JJJ	20010801	Delhi	Twins
4000	SSS	YYY	20010801	Delhi	Twins

I was expecting this result

ID	Name	Surname	Birthday	Town
1000	AAA	BBB	19000101	Frankfurt
1000	AAA	BBB	19201006	London
2000	HHH	GGG	null	Bogota
3000	MMM	RRR	19620723	Null
4000	TTT	JJJ	20010801	Delhi
4000	SSS	YYY	20010801	Delhi

Instead i'm getting result below :

id	name	surname	birthday	town
1000	AAA	BBB	19000101	Frankfurt
1000	AAA	BBB	19201006	London
1000	AAA	BBB	Null	null
2000	HHH	GGG	null	Bogota
3000	MMM	RRR	19620723	null
3000	MMM	RRR	null	null
4000	SSS	YYY	20010801	Delhi
4000	TTT	JJJ	20010801	Delhi
4000	TTT	JJJ	null	Delhi

I've used same query provided by @"mathguy", which was really helpfull

select distinct id, name, surname,

nvl(birthday, max(birthday) over (partition by id, name, surname)) as birthday

from sample_data

order by id, name, surname, birthday -- if needed

;

But it's not retrieving the good results when adding new data.

I've tried using min(id) ... and regroup but nothing change

If anyone can help, that would be great

regards

Carlos

Attached file the script to create dataset.

Oracle Version 11g2

This post has been answered by mathguy on Oct 4 2018

Jump to Answer

Added on Oct 4 2018

#sql-pl-sql

11 comments

1,118 views