NULLS are treated as high priority when assigned row numbers with in a group

Database
KVB
Enthusiast

NULLS are treated as high priority when assigned row numbers with in a group

Hi

I have the below table and I am trying to assign rownumber and need to get row_number=1 for the values.But theere are NULL's in the group  where they are being treated high priority and 1 is assigned to it.

Let me know if there is any option to treat this.

CT KVB1(EMPNO INTEGER,ENAME VARCHAR(10),DNO INTEGER,DNAME VARCHAR(10));

INS INTO KVB1 VALUES(1,'A',10,NULL);

INS INTO KVB1 VALUES(1,'A',NULL,NULL);

INS INTO KVB1 VALUES(2,'B',10,NULL);

INS INTO KVB1 VALUES(3,'C',NULL,NULL);

SEL EMPNO,ENAME,DNO, ROW_NUMBER() OVER(PARTITION BY EMPNO,ENAME ORDER BY DNAME DESC) RN FROM KVB1

Rownumber 1 is being assigned to NULL values.But in Teradata NULLS are treated as least.

Or is there any option in Teradata to treat the NULLS at last

Regards

KVB

2 REPLIES
KVB
Enthusiast

Re: NULLS are treated as high priority when assigned row numbers with in a group

I got it.

I placed in a derived table and applied rownum and it worked out.

Junior Contributor

Re: NULLS are treated as high priority when assigned row numbers with in a group

A Derived Table shouldn't change that, when you sort descending NULLs are sorted last.

In your example you ordered by DNAME and all rows had NULL in it, maybe you ment DNO.

For changing the default sort order of NULLs you have to use "CASE WHEN col IS NULL THEN 1 ELSE 1 END, col DESC" or wait for TD14.10, which adds NULLS FIRST/LAST :-)

Dieter