Re: Sorting update?

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 23 Sep 1999 18:44:57 GMT
Message-ID: <7sdsf9$fqm$9@news.seed.net.tw>

Thorsten Koch <torsti_engy_at_my-deja.com> wrote in message news:7s8gpk$mnb$1_at_nnrp1.deja.com...
> Hi folks!
>
> Let there be table EMP with
> POS NUMBER
> NAME VARCHAR2
> SAL NUMBER
>
> and the data
> POS NAME SAL
> ----- ------ -----
> 1 A 1000
> 2 C 500
> 3 B 700
>
> Now I want to update the table in a way that its sorted by salaries, so
> that it looks like this:
>
> POS NAME SAL
> ----- ------ -----
> 1 C 500
> 2 B 700
> 3 A 1000
>
> That is, I would like to have an update-statement like
>
> update emp
> set pos=(select rownum
> from emp
> order by sal);
>
> Unfortunately, it is not possible to use "order by" in an update
> statement.
>
> Can someone advise me how to perform this task? I would be glad to
> receive a few different solutions, because not all solutions may fit to
> my special application.

SQLWKS> update emp a

     2> set pos=(select count(*)+1 from emp b where a.sal>b.sal); 14 rows processed.
SQLWKS> select pos, ename, sal from emp order by pos; POS ENAME SAL
---------- ---------- ----------

         1 SMITH             800
         2 JAMES             950
         3 ADAMS            1100
         4 WARD             1250
         4 MARTIN           1250
         6 MILLER           1300
         7 TURNER           1500
         8 ALLEN            1600
         9 CLARK            2450
        10 BLAKE            2850
        11 JONES            2975
        12 SCOTT            3000
        12 FORD             3000
        14 KING             5000

14 rows selected. Received on Thu Sep 23 1999 - 13:44:57 CDT