Archives of the TeradataForum

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

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

Anomy Anom wrote:

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