

Archives of the TeradataForum
Message Posted: Thu, 12 Jan 2012 @ 23:39:23 GMT
Subj:   Re: Issue getting a rank 

From:   Galeazzi, Debbie 
I received the following examples from another engineer. The join is needed if the columns are nullable.
Sel * from t1 order by g, x;
*** Query completed. 12 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
g x y
  
? 2 3
A ? ?
A 1 1
A 2 2
A 2 9
A 2 8
A 3 3
A 10 4
B 0 5
B 11 6
B 12 7
C 1 2
BTEQ  Enter your DBC/SQL request or BTEQ command:
Sel t1.g, t1.x, dense_rank, rank() over (partition by t1.g order by t1.x)
From t1, (select g, x, rank() over (partition by g order by x) From t1
Group by g, x) t1dense(g, x, dense_rank) Where ((t1.g = t1dense.g) or (t1.g is null and
t1dense.g is null)) and ((t1.x = t1dense.x ) or (t1.x is null and t1dense.x is null))
Order by t1.g, t1.x;
*** Query completed. 12 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
g x dense_rank Rank(x ASC)
   
? 2 1 1
A ? 1 1
A 1 2 2
A 2 3 3
A 2 3 3
A 2 3 3
A 3 4 6
A 10 5 7
B 0 1 1
B 11 2 2
B 12 3 3
C 1 1 1
BTEQ  Enter your DBC/SQL request or BTEQ command:
Sel t1.g, dense_rank, rank() over (order by t1.g) From t1, (select g, rank() over (order
by g) From t1 Group by g) t1dense(g, dense_rank) Where ((t1.g = t1dense.g) or (t1.g is
null and t1dense.g is null)) Order by t1.g;
*** Query completed. 12 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
g dense_rank Rank(g ASC)
  
? 1 1
A 2 2
A 2 2
A 2 2
A 2 2
A 2 2
A 2 2
A 2 2
B 3 9
B 3 9
B 3 9
C 4 12
 