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
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 :-)