Re: SQL Problem

From: Odd Morten Sve?s <odd.morten.sveas_at_accenture.com>
Date: 18 Jul 2004 13:58:17 -0700
Message-ID: <4306a83.0407181258.42bcbc03@posting.google.com>

ak_tiredofspam_at_yahoo.com (AK) wrote in message news:<46e627da.0407161030.42e5e818_at_posting.google.com>...
> CASE WHEN ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY SCORE) = 1 THEN
> NAME ELSE NULL END
Hi

Is can this be runn on Oracle? I would be pleased to know more about this type of syntax.

In a traditionel but of cource cumbersome syntax the following will do the trick. (Not tested, so it migth be some syntax errors)

insert into b
(name,score)
select decode(rowid ,minrowid, name , null) , score
(select rowid

,   name
,   score
, (select min(rowid) from a a2 where a2.name = a1.name ) minrowid

from a a1)

But the simples would be to write an pl/sql prgram to solve this, includinga cursor, a loop and a check if the name has changed.

Regards
Odd M Received on Sun Jul 18 2004 - 15:58:17 CDT