Re: [Q] Sorting a column
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 08 Aug 2008 12:26:31 -0700
Message-ID: <1218223583.133026@bubbleator.drizzle.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.)
Date: Fri, 08 Aug 2008 12:26:31 -0700
Message-ID: <1218223583.133026@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.orgReceived on Fri Aug 08 2008 - 14:26:31 CDT