Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx01.iad01.newshosting.com!newshosting.com!198.186.194.249.MISMATCH!transit3.readnews.com!news-out.readnews.com!news-xxxfer.readnews.com!panix!panix.com!tool
From: tool@panix.com (Dan Blum)
Newsgroups: comp.databases.oracle.misc
Subject: Re: [Q] Sorting a column
Date: Fri, 8 Aug 2008 15:26:11 +0000 (UTC)
Organization: PANIX Public Access Internet and UNIX, NYC
Lines: 32
Message-ID: <g7hoii$ra0$2@reader1.panix.com>
References: <1210a149-d43e-48a8-bbd7-9a688c96fa88@y38g2000hsy.googlegroups.com> <489beea6$0$49829$e4fe514c@news.xs4all.nl>
NNTP-Posting-Host: panix1.panix.com
X-Trace: reader1.panix.com 1218209171 27968 166.84.1.1 (8 Aug 2008 15:26:11 GMT)
X-Complaints-To: abuse@panix.com
NNTP-Posting-Date: Fri, 8 Aug 2008 15:26:11 +0000 (UTC)
User-Agent: tin/1.8.3-20070201 ("Scotasay") (UNIX) (NetBSD/4.0 (i386))
Xref: usenetserver.com comp.databases.oracle.misc:253484
X-Received-Date: Fri, 08 Aug 2008 11:26:11 EDT (text.usenetserver.com)

Shakespeare <whatsin@xs4all.nl> wrote:

> "digory" <digory@gmx.net> schreef in bericht 
> news:1210a149-d43e-48a8-bbd7-9a688c96fa88@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)

Only if they're in a cluster - rows that have the same cluster key have
the same ROWID (or so it says in the docs, I haven't tried it). 

-- 
_______________________________________________________________________
Dan Blum					         tool@panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."