Hello,
I have questions regarding distinct operation and would be glad if someone could help me out.
Consider the following table (mytable):
id, name
1, A
1, A
2, B
3, A
1, A
If we do select avg(id) over (partition by name) from mytable, partition logic goes like this:
for A: 1, 1, 3, 1
If we want to implement something like this select avg(distinct id) over (partition by name) from mytable
and remove duplicate by storing last datum of aggregate column (id) and comparing it with current value. It fails here because aggregate column is not sorted within the partition.
Questions:
1. Is sorting prerequisite for finding distinct values?
2. Is it okay to sort aggregate column (within partition) for distinct to work in case of window function?
3. Is an alternative way exists to handle this scenario (because here sort is of no use in aggregation)?
Thanks
--
Regards,
Ankit Kumar Pandey