Re: [Q] Sorting a column

Shakespeare wrote:

> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht 
> news:1218586746.595453_at_bubbleator.drizzle.com...

>>> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht 
>>> news:1218223583.133026_at_bubbleator.drizzle.com...
>>>> Shakespeare wrote:
>>>>> "digory" <digory_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
>>>> damorgan_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

> 
> So there is still a SLIGHT possibility two tables may have rows with a 
> duplicated rowid (by coincidence) after all?
> 
> Shakespeare 

No that would be impossible. A rowid is a pointer to a specific row in a specific block in a specific data file. Consider what would happen if Oracle were to read an index, grab a rowid, and didn't know where to look for the data. That isn't going to happen. What appears to be possible is that two rows in different tables in the same cluster, thus the same block, might have the same rowid.

Thus I would disagree with your conclusion.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org