Re: Sorting update?

A copy of this was sent to Thorsten Koch <torsti_engy_at_my-deja.com> (if that email address didn't require changing) On Tue, 21 Sep 1999 17:55:08 GMT, you wrote:

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

this is a *little* different from what you asked for in that if 2 people have the SAME salary -- i'll assign them the same ranking. For example:

tkyte_at_8.0> update emp

  2     set pos = ( select count(*) 
  3                   from emp b
  4                  where b.sal < emp.sal );

14 rows updated.

  1* select empno, pos, sal from emp order by pos tkyte_at_8.0> /

     EMPNO POS SAL
---------- ---------- ----------

      7369          0        800
      7900          1        950
      7876          2       1100
      7521          3       1250
      7654          3       1250
      7934          5       1300
      7844          6       1500
      7499          7       1600
      7782          8       2451
      7698          9       2850
      7566         10       2975
      7788         11       3000
      7902         11       3000
      7839         13       5000

That has 2 #11's because they both make 3,000 (note that there is NO number 12 -- just 2 #11's).

It does a nice ranking for you if thats what you want... Some people might say this is 'un-natural' as there really isn't any 'order' to tuples in a database table but it can be useful to do real world things like generate top-n reports and rankings....

>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.
>
>Many thanks,
>
>Thorsten
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 21 1999 - 14:43:00 CDT