Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sun, 24 Jul 2005 @ 16:14:12 GMT


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


Subj:   Re: Rank and dense_rank
 
From:   Dieter Noeth

Anomy Anom wrote:

  The ANSI function name for "dense_rank" is ROW_NUMBER.  


No, dense rank has not been implemented (probably because of performance reasons), but can be rewritten:

     /* not implemented */
     sel
        department_number,
        dense_rank() over
          (partition by department_number order by salary_amount)
        last_name,
        salary_amount
     from employee;


     /* Replacement */
     sel
        e.department_number,
        dt.rnk,
        e.last_name,
        e.salary_amount
     from cs_views.employee e
     join
       (
        select
          salary_amount,
          rank() over (ORDER BY salary_amount) as rnk
        from cs_views.employee
        group by 1
       ) dt
     on e.salary_amount = dt.salary_amount
     order by rnk, e.salary_amount
     ;

Dieter



     
  <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: 15 Jun 2023