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>

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