partition By column contains "NULL"

Database
Enthusiast

partition By column contains "NULL"

Hi All,

When one of the partition By columns have Nulls will it lead to a new partition. For e.g.

Col#1 Col#2 Col#3 Col#4(date)
1 2 null 12-05-2008
1 2 null 11-07-2008
1 3 null 08-07-2008

Select
t1.*,
Rank() over(partition by Col#1,Col#2 order by Col#4 asc)
from table t1;

the answer to the above query is
Col#1 Col#2 Col#3 Col#4(date) Rank
1 2 null 12-05-2008 1
1 2 null 11-07-2008 2
1 3 null 08-07-2008 1

However if we include Col#3 in the partition as well the result remains the same.

Select
t1.*,
Rank() over(partition by Col#1,Col#2,Col#3 order by Col#4 asc)
from table t1;

the answer to the above query is
Col#1 Col#2 Col#3 Col#4(date) Rank
1 2 null 12-05-2008 1
1 2 null 11-07-2008 2
1 3 null 08-07-2008 1

I thought 1,2,null(record 1) will be one partition and 1,2,null(record 2) will be another partition. When "Null" is Unknown and no two nulls aren't same then how come the partition logic is same.

Any idea....
2 REPLIES
Junior Contributor

Re: partition By column contains "NULL"

OLAP function follow the same rules as aggregates and UNION: NULLs are treated equal.

select Col#1,Col#2,Col#3
group by 1,2,3

Dieter
Enthusiast

Re: partition By column contains "NULL"

Thanks Dieter,

The moment i posted the question i was sure of this answer. it will help if we get to know how a RDBMS make sure a NULL when aggregated should be considered as equal but in comparison operators they should be different...

Cheers,
novice