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