Re: Sorting a column
From: <fitzjarrell_at_cox.net>
Date: Thu, 7 Aug 2008 08:05:41 -0700 (PDT)
Message-ID: <d99f1460-a6ee-4c26-bf07-b9e289cdac53@25g2000hsx.googlegroups.com>
end loop;
SMITH
SMYTHE
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER 15 rows selected.
13 end;
14 /
Date: Thu, 7 Aug 2008 08:05:41 -0700 (PDT)
Message-ID: <d99f1460-a6ee-4c26-bf07-b9e289cdac53@25g2000hsx.googlegroups.com>
On Aug 7, 3:48 am, digory <dig..._at_gmx.net> wrote:
> I just tried the following
>
> UPDATE T
> SET pos = DENSE_RANK () OVER (ORDER BY name)
>
> Unfortunately, this does not work because Oracle does not allow
> analytic functions in the SET clause.
This does:
declare
cursor get_rank is]
select name, dense_rank() over (order by name) dr
from t;
begin
for gr in get_rank loop
update t set pos = gr.dr where name = gr.name;
end loop;
commit;
end;
/
Here's proof:
SQL> select * From t;
NAME POS -------------------- ----------
SMITH
SMYTHE
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
NAME POS -------------------- ----------
ADAMS
JAMES
FORD
MILLER 15 rows selected.
SQL> declare
2 cursor get_rank is 3 select name, dense_rank() over (order by name) dr 4 from t; 5 begin 6 for gr in get_rank loop 7 update t 8 set pos = gr.dr 9 where name = gr.name; 10 end loop; 11 12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> select * from t;
NAME POS -------------------- ---------- SMITH 12 SMYTHE 13 ALLEN 2 WARD 15 JONES 7 MARTIN 9 BLAKE 3 CLARK 4 SCOTT 11 KING 8 TURNER 14 NAME POS -------------------- ---------- ADAMS 1 JAMES 6 FORD 5 MILLER 10
15 rows selected.
SQL> David Fitzjarrell Received on Thu Aug 07 2008 - 10:05:41 CDT