exilrhoener wrote:
> 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?
ROW_NUMBER() OVER()
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Received on Tue Feb 07 2006 - 08:41:05 CST
