Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> renumbering with rownum

renumbering with rownum

From: exilrhoener <threesheep_at_gmx.de>
Date: 7 Feb 2006 05:46:20 -0800
Message-ID: <1139319980.006267.180770@z14g2000cwz.googlegroups.com>


Hi,

i have a table like

ID    POS        DATE        FILTER
1      1        01.01.01       x
3      5        02.01.01       x
4      2        01.02.02       y
5      6        01.03.04       x
12     6        13.04.03       x

i try to renumber the column POS with plain sql, so that pos is incremental (order by pos, date where filter="x"). So it should look like

D    POS        DATE        FILTER
1      1        01.01.01       x
3      2        02.01.01       x
4      2        01.02.02       y
5      4        01.03.04       x
12     3        13.04.03       x

I was successful with pl/sql and a cursor, but there has to be a way to do this by sql...

I tried:
UPDATE tbl a SET pos=
(

	SELECT ROWNUM FROM
		(SELECT ID FROM TBL_PRO_ADD
		WHERE FILTER like 'x'
		 ORDER BY pos, date)  b
	 WHERE b.id = a.id

)

but this led to every pos = 1, which is obvious because the correlating subquery contains only 1 record.

Any Hints?

Thank you

Stefan Received on Tue Feb 07 2006 - 07:46:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US