Archives of the TeradataForum

Message Posted: Sun, 24 Jul 2005 @ 16:37:13 GMT

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

Dieter Noeth wrote:

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

This time the right version:

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

Dieter

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