Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 12 Jan 2012 @ 23:39:23 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


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


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016