Re: Sorting a column

On Aug 12, 3:33 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "DA Morgan" <damor..._at_psoug.org> schreef in berichtnews:1218223583.133026_at_bubbleator.drizzle.com...
>
>
>
>
>
> > Shakespeare wrote:
> >> "digory" <dig..._at_gmx.net> schreef in bericht
> >>news:1210a149-d43e-48a8-bbd7-9a688c96fa88_at_y38g2000hsy.googlegroups.com...
> >>> Hi
>
> >>> I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
> >>> want to write an UPDATE query, which updates the column POS such that
> >>> its values correspond to the alphabetical order of NAME.
>
> >>> UPDATE
> >>>   T t1
> >>> SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
> >>> NAME)
>
> >>> That does not work, because the WHERE clause returns a single row,
> >>> which will always have a ROWID of 1.
>
> >>> How do I do this? (It's possible with a PROCEDURE, of course, but I
> >>> want to avoid them.)
>
> >> ROWID? Don't you mean rownum?
> >> I don't think tables will ever have rows with the same rowid (except by
> >> coincidence)
>
> >> Shakespeare
>
> > Not even by coincidence ... it is a technical impossibility.
> > --
> > Daniel A. Morgan
> > Oracle Ace Director & Instructor
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Group
> >www.psoug.org
>
> Just by curiousity (and too lazy to look in the manuals...): does Oracle
> change ROWID's when a transportable tablespace is moved from one system to
> an other?
>
> Shakespeare

I would think not, otherwise you'd have a lot of index rebuilding to do! See the concepts manual about rowid structure and physical v. logical rowids, and the administrator's guide about limitations of transportable tablespaces. You need to check that the transport set is self-contained, which among other things means that any indices don't point out or into the set. Physical rowid's are considered opaque, opaque datatypes are not converted even across endianness.

Also check out the rowid_info procedure - note that the file number is relative.

jg

--
@home.com is bogus.
Duck and Cover!  http://www.trustedsource.org/blog/142/New-SQL-Injection-Attack-Infecting-Machines