Re: Sorting a column

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 14 Aug 2008 21:58:06 +0200
Message-ID: <48a48e51$0$191$e4fe514c@news.xs4all.nl>

"joel garry" <joel-garry_at_home.com> schreef in bericht news:b45a200c-cb82-4997-8ece-9a4e82d8f007_at_l33g2000pri.googlegroups.com...

> On Aug 13, 12:26 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:

>> "joel garry" <joel-ga..._at_home.com> schreef in
>> berichtnews:8ab2e84c-704f-48f9-8479-46218e64d68e_at_r35g2000prm.googlegroups.com...
>> On Aug 12, 10:55 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
>>
>>
>>
>>
>>
>> > "DA Morgan" <damor..._at_psoug.org> schreef in
>> > berichtnews:1218586746.595453_at_bubbleator.drizzle.com...
>>
>> > > Shakespeare wrote:
>> > >> "DA Morgan" <damor..._at_psoug.org> schreef in bericht
>> > >>news: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 can't check it right now but it seems to me it would have no choice
>> > > if it found a conflict. Whether it checks or just changes them I can
>> > > not say without looking.
>> > > --
>> > > 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
>>
>> > So there is still a SLIGHT possibility two tables may have rows with a
>> > duplicated rowid (by coincidence) after all?
>>
>> > Shakespeare
>>
>> I still don't think so. Look at what you can find out about a rowid:
>>
>> DBMS_ROWID.ROWID_INFO (
>> rowid_in IN ROWID,
>> ts_type_in IN VARCHAR2 DEFAULT 'SMALLFILE',
>> rowid_type OUT NUMBER,
>> object_number OUT NUMBER,
>> relative_fno OUT NUMBER,
>> block_number OUT NUMBER,
>> row_number OUT NUMBER);
>>
>> All those OUT's would have to be the same for a duplication. How
>> could you have a table in two different tablespaces? desc
>> dba_tables. The relative file number makes it unique within that
>> tablespace.
>>
>> Another clue may be found
>> inhttp://www.jlcomp.demon.co.uk/book_8i/ch_08.html#Complex%20Transporta...
>> . Unless there is some way to fool Oracle into importing various
>> files from different same-named tablespaces at different times... but
>> then, you could use an editor on the datafiles when Oracle is shut
>> down if you are that devious, no need to bother with transport at
>> all. I wonder what happens when you try to rebuild the index.
>>
>> jg
>> --
>> @home.com is bogus.
>> Number one on the McCain ipod list: Dancing Queen by ABBA
>>
>> ========================================================
>> So I guess this guy is talking crap?
>> (seehttp://www.adp-gmbh.ch/ora/concepts/rowid.html)
>> <quote>
>> A rowid identifies a row in a table
>> A rowid is a pseudo column (like versions_xid), that uniquely identifies
>> a
>> row within a table, but not within a database. It is possible for two
>> rows
>> of two different tables stored in the same cluster to have the same
>> rowid.
>>
>> <unquote>
>>
>> Shakespeare

>
> I'm sorry, I misread your earlier post, missed the part about two
> tables.  I'm not sure why it matters if different tables have the same
> rowid?  Since O8, the extended rowid has the object number.

Well, it's just because Daniel said it was impossible... ;-) but I seem to have been a bit unclear about the two different tables...

>
> Of course, the concepts manual defines a rowid as a globally unique
> identifier in the database, while the part about physical rowid's
> mentions that two tables in the same block of a cluster can have the
> same rowid.  I suspect that is only true with restricted rowid's
> (read: Oracle7), or maybe it just means the two tables share the same
> columns, but we'll just have to wait for someone to actually try it to
> see.  See 
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3921
>
> jg
> --
> @home.com is bogus.
> If the New York Times splashed across their front page about the first
> cyberattack that coincided with a shooting war, would anyone care?
> http://www.signonsandiego.com/uniontrib/20080813/news_1n13cyberwar.html

Shakespeare Received on Thu Aug 14 2008 - 14:58:06 CDT